Convert Relative and Absolute References in a formula

[nagovind]

Below code is working well to revolve arround the Relative and Absolute References


However the issue is it is converting all formulas in a sheet


Is it possible to run a code ONLY ON THE SELECTED cell ?

Please advise

[pre]

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

[/pre]

 

[NARAYANK991]

Hi Govind ,


The procedure works on all the formula cells in the sheet only if you do not select at least two cells.


If you select at least two cells , and then run the procedure , it works on only the selected cells.


The two cells you select need not both have formulae ; only one of them can have formulae. Of course , if no cell has any formulae , the procedure generates an error.


Narayan

 

[nagovind]

Thanks Narayan