F4 Toggle Macro Performance/Tweak

[irie267]

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.

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

 

[DanteAmor]

Hi @irie267 . I hope you are well.

Try this

Sub F4_Cycle()
  Dim oneCell As Range
  Static absRelMode As Long
 
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
 
  absRelMode = (absRelMode Mod 4) + 1
  For Each oneCell In Selection
    oneCell.FormulaR1C1 = Application.ConvertFormula(oneCell.FormulaR1C1, xlR1C1, xlR1C1, absRelMode, oneCell)
  Next
 
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

 

[irie267]

Hi @irie267 . I hope you are well.

Try this

Sub F4_Cycle()
  Dim oneCell As Range
  Static absRelMode As Long
 
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
 
  absRelMode = (absRelMode Mod 4) + 1
  For Each oneCell In Selection
    oneCell.FormulaR1C1 = Application.ConvertFormula(oneCell.FormulaR1C1, xlR1C1, xlR1C1, absRelMode, oneCell)
  Next
 
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

This is perfect--thank you so much.