Forum Discussion

Arrie1960

Copper Contributor

Jul 10, 2022

Formulas and Functions $ Automatic Fill

How do I automatically fill a spreadsheet with the $ function that refer to Exact Cells

Formulas and Functions

Arrie1960

Copper Contributor

Jul 11, 2022

Thanks but I am looking for the solution for the $ sign as a precise cell such as $B$7 for example to auto Complete sheets

HansVogelaar

MVP

Jul 11, 2022

Arrie1960

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

Copper Contributor

Jul 11, 2022

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 ,

HansVogelaar

MVP

Jul 11, 2022

Arrie1960

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