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.

Have a look in vba help index for CONVERTFORMULA

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Axel" <(E-Mail Removed)> wrote in message
news:4907E2CC-084C-4C0D-B965-(E-Mail Removed)...
>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

Don Guillett wrote:
> Have a look in vba help index for CONVERTFORMULA
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Axel" <(E-Mail Removed)> wrote in message
> news:4907E2CC-084C-4C0D-B965-(E-Mail Removed)...
> >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

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 <(E-Mail Removed)> 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

glad to help

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Axel" <(E-Mail Removed)> wrote in message
news:F616C8B6-68B3-43B6-AE8D-(E-Mail Removed)...
> Thanks a lot Don and Pete! This helped.
>
> "Don Guillett" wrote:
>
>> Have a look in vba help index for CONVERTFORMULA
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Axel" <(E-Mail Removed)> wrote in message
>> news:4907E2CC-084C-4C0D-B965-(E-Mail Removed)...
>> >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.
>>
>>
>>