Changing formulas from relative to absolute

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have many already existing formulas in a sheet, now I'm looking for an easy
way to change all these formulas from relative to absolute.
 
You can use Find and Replace, but you might need to apply it several
times. Many cell references will be like this in a formula:

=function(A1)

so you can search for "(A" (no quotes) and replace with "($A$" (no
quotes).

Often you have cell references following a comma, like =SUM(A1,C1,E1),
so in a case like this you can search for ",C" and replace with ",$C$".

The second reference in a range will follow a colon, so you can search
for ":A" and replace with ":$A$".

Hope this helps.

Pete
 
Axel

Will a macro solution be OK with you?

Try these. Ignores cells without formulas.

Sub Absolute()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub


Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub


Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub


Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelative)
End If
Next
End Sub


Gord Dibben MS Excel MVP
I have many already existing formulas in a sheet, now I'm looking for an easy
way to change all these formulas from relative to absolute.
Click to expand...

Gord Dibben MS Excel MVP
 
Back
Top