Wednesday, June 20, 2012

VBA multiple cells static reference

Credit http://www.ozgrid.com/forum/showthread.php?t=86661
'Select a range of sells and run, it will make all cell references static

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

No comments: