Changing formulas from relative to absolute

[Axel]

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.

[Don Guillett]

Have a look in vba help index for CONVERTFORMULA

--
Don Guillett
SalesAid Software
[email protected]
"Axel" <[email protected]> wrote in message
news:[email protected]...
>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.

[Pete_UK]

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
> [email protected]
> "Axel" <[email protected]> wrote in message
> news:[email protected]...
> >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.

[Axel]

Thanks a lot Don and Pete! This helped.

"Don Guillett" wrote:

> Have a look in vba help index for CONVERTFORMULA
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Axel" <[email protected]> wrote in message
> news:[email protected]...
> >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]

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 <[email protected]> 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

[Don Guillett]

glad to help

--
Don Guillett
SalesAid Software
[email protected]
"Axel" <[email protected]> wrote in message
news:[email protected]...
> Thanks a lot Don and Pete! This helped.
>
> "Don Guillett" wrote:
>
>> Have a look in vba help index for CONVERTFORMULA
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Axel" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>>
>>
>>