Formulas and Functions $ Automatic Fill

Question

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

nikolinode · Answer

select table column,right mousekeyformat cells...Numbers --&gt; CurrencyIcon: click dropdownChoose preferred currencyOk...and it's done!

arrie1960 · Answer

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 · Answer

Arrie1960&nbsp;
If you select a cell or range reference in a formula, pressing function key F4 toggles between the absolute/relative options:
&nbsp;
B7 will change to $B$7, then to B$7, next to $B7 and then back to B7.

arrie1960 · Answer

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 · Answer

Arrie1960&nbsp;
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

Forum Discussion

Formulas and Functions $ Automatic Fill

5 Replies