Making all Formulas in a range Absolute Refrences - Is this possible?

[Jon Johny]

Is there any way to make all formuals in certain range absolute refrences?

I want to do this becasue I want to use the sort function in a table, and if the formuals are not absolute refreces and I perform a sort the values change.

 

[Gerald Higgins]

You MIGHT be able to use Edit, Find and Replace, but how easy this will be depends what your formulas look like.

For example, if your formulas are all of the form

+A1

you could use Edit, Find all instances of A, and replace them all with $A$
But this will be more difficult if your formulas are more complex.

 

[mikerickson]

This routine should do that, but if the formula uses a name that uses relative references, it won't change that

Sub test()
    Dim oneCell
    On Error Resume Next
    For Each oneCell In Cells.SpecialCells(xlCellTypeFormulas)
        With oneCell
            .FormulaR1C1 = Application.ConvertFormula(.FormulaR1C1, xlR1C1, xlR1C1, True)
        End With
    Next oneCell
    On Error GoTo 0
End Sub