Formulas and Functions $ Automatic Fill

New Contributor

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

5 Replies
select table column,
right mousekey
format cells...
Numbers --> Currency
Icon: click dropdown
Choose preferred currency
Ok...and it's done!
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

@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.

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 ,

@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