Forum Discussion

joeyc380's avatar
joeyc380
Copper Contributor
Oct 06, 2024

A macro to cycle through relative/ absolute/ mixed reference for multiple cells

Hi everyone,

 

I have a macro use to cycle through relative and absolute reference for multiple cells:

Sub CycleAbsRel()
Dim inRange As Range, oneCell As Range
Static absRelMode As Long
absRelMode = (absRelMode Mod 4) + 1

Set inRange = Selection.SpecialCells(xlCellTypeFormulas)

If Not (inRange Is Nothing) Then
For Each oneCell In inRange
With oneCell
.FormulaR1C1 = Application.ConvertFormula(.FormulaR1C1, xlR1C1, xlR1C1, absRelMode, oneCell)
End With
Next oneCell
End If
End Sub

 

This work well for non-array formula.

 

However, when I apply this to an array formula, e.g. =G2:G6, when I apply the macro, it will add an @ to the formula, such as =@G$2:G$6; =@$G$2:$G$6; =@$G2:$G6.

 

Anyone can give me some help to modify the code, so that it can apply to array formula to cycle through the relative/ absolute/ mixed cell reference, without add the @?

 

Many thanks!

Macros and VBA
    • joeyc380's avatar
      joeyc380
      Copper Contributor
      Oct 08, 2024

      HansVogelaar 

       

      Sub CycleAbsRel()
    Dim inRange As Range, oneCell As Range
    Static absRelMode As Long
    absRelMode = (absRelMode Mod 4) + 1
     
    Set inRange = Selection.SpecialCells(xlCellTypeFormulas)
     
    If Not (inRange Is Nothing) Then
        For Each oneCell In inRange
            With oneCell
                .Formula2R1C1 = Application.ConvertFormula(.Formula2R1C1, xlR1C1, xlR1C1, absRelMode, oneCell)
            End With
        Next oneCell
    End If
End Sub

      Thanks.

      Change to this but sadly it continues to add @ in my formula like =@$H12:$H15 

Resources