Hello all--
The macro below allows the user to toggle through the F4 absolute/relative reference feature across a range of selected cells. It's a great macro to have but there are two outstanding issues: 1) if only one cell is selected, the macro takes it upon itself to begin F4 toggling through every formula in the sheet, which is an outcome that isn't desired (would like it to be "turned off" when just one cell is selected), 2) the performance of this macro, even when only a few formulas are selected, is extremely slow. Any assistance in resolving these issues would be greatly appreciated.
The macro below allows the user to toggle through the F4 absolute/relative reference feature across a range of selected cells. It's a great macro to have but there are two outstanding issues: 1) if only one cell is selected, the macro takes it upon itself to begin F4 toggling through every formula in the sheet, which is an outcome that isn't desired (would like it to be "turned off" when just one cell is selected), 2) the performance of this macro, even when only a few formulas are selected, is extremely slow. Any assistance in resolving these issues would be greatly appreciated.
VBA Code:
Sub F4_Cycle()
'
' F4_Cycle Macro
'
' Keyboard Shortcut: Ctrl+Shift+/
'
Application.ScreenUpdating = False
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
Application.ScreenUpdating = True
End Sub