Jul 10 2022 07:07 AM
How do I automatically fill a spreadsheet with the $ function that refer to Exact Cells
Jul 10 2022 08:27 AM
Jul 10 2022 10:21 PM
Jul 11 2022 03:45 AM
If you select a cell or range reference in a formula, pressing function key F4 toggles between the absolute/relative options:
B7 will change to $B$7, then to B$7, next to $B7 and then back to B7.
Jul 11 2022 06:30 AM
Jul 11 2022 06:44 AM
You could run the following macro (test on a copy of the workbook first!)
Sub Convert2Absolute()
Dim wsh As Worksheet
Dim rng As Range
Dim cel As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each wsh In Worksheets
Set rng = Nothing
On Error Resume Next
Set rng = wsh.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cel In rng
cel.Formula = Application.ConvertFormula(cel.Formula, xlA1, , xlAbsolute)
Next cel
End If
Next wsh
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub