Automatically change formulas of lots of cells from relative to absolute reference

[Norbert]

Hi,
is it somehow possible to change formulas for a huge array of cells from
relative to absolute reference?

Any help is highly appreciated. I can't do this all by hand!

I've tried to record a macro but it didn't work the way I thought.

Thanks,
Norbert

[Claus Busch]

Hi Norbert,

Am Wed, 24 Aug 2022 17:02:46 +0200 schrieb Norbert:

> is it somehow possible to change formulas for a huge array of cells from
> relative to absolute reference?

try:

Sub Convert_Formula()
Dim myRng As Range
Dim i As Long

Set myRng = ActiveSheet.UsedRange.SpecialCells(Type:=xlFormulas)

For i = 1 To myRng.Areas.Count
myRng.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=myRng.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
Next
End Sub


Regards
Claus B.
--
Windows10
Microsoft 365 for business

[Norbert]

Hi Claus,

thank you very much! I worked like a charm!
Sorry for only coming back now, but I didn't have a chance to test it
until now.

Regards,
Norbert