Forum Discussion
Arrie1960
Jul 10, 2022Copper Contributor
Formulas and Functions $ Automatic Fill
How do I automatically fill a spreadsheet with the $ function that refer to Exact Cells
HansVogelaar
Jul 11, 2022MVP
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.
Arrie1960
Jul 11, 2022Copper Contributor
Thanks that is true what you say but if you have 225 cells to page on one sheet and 31 sheets then it becomes a big job ,
- HansVogelaarJul 11, 2022MVP
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