Changing formulas from relative to absolute
 

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.

Changing formulas from relative to absolute
 

Have a look in vba help index for CONVERTFORMULA

--
Don Guillett
SalesAid Software

"Axel" wrote in message
...
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.

Changing 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

Don Guillett wrote:
Have a look in vba help index for CONVERTFORMULA

--
Don Guillett
SalesAid Software

"Axel" wrote in message
...
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.

Changing formulas from relative to absolute
 

Thanks a lot Don and Pete! This helped.

"Don Guillett" wrote:

Have a look in vba help index for CONVERTFORMULA

--
Don Guillett
SalesAid Software

"Axel" wrote in message
...
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.

Changing formulas from relative to absolute
 

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
On Wed, 14 Jun 2006 05:03:02 -0700, Axel wrote:

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.

Gord Dibben MS Excel MVP

Changing formulas from relative to absolute
 

glad to help

--
Don Guillett
SalesAid Software

"Axel" wrote in message
...
Thanks a lot Don and Pete! This helped.

"Don Guillett" wrote:

Have a look in vba help index for CONVERTFORMULA

--
Don Guillett
SalesAid Software

"Axel" wrote in message
...
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.