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!

​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!  Read More

​