Convert a range of cells formulas from relative to absolute

Hi Corni:

I am not quite sure about your inquiry, however, could you make use of the ADDRESS function ... see the worksheet simulation ...</SPAN>

If this is of no use -- my apologies.

Thank you for the response, but this is not what I had in mind. I need to change from relative to absolute all formulas existing in a big range of cells, a whole worksheet actually, some of them complex and lengthy. That is the reason that I cannot afford to use F4 functionality, which will be time consuming.

Hi Corni.
I figured out a way to automate what you are doing but I'm not willing to fire up the code to do it. I just thought I would let you know incase you want to give it a shot if it's worth it.

FormulaAddresses = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).DirectPrecedents.Address(0, 0)

Would gather all of the A1 style addresses on the worksheet. Using VBA text functions such as InStr and InStrRev you could then search DirectDependants by breaking down the string methodically, rebuild the string testing for the numeric rows as opposed to the alpha columns, use find/replace to replace relative references with absolute references. Of course an obvious drawback would be the fact that some of your formulas may happen to contain text which might equal the find string. You could further enhance the accuracy by testing for what usually surrounds or is part of a range reference such as "(" or ")" or ":" or "," ect...
A somewhat complicated piece of code and too late for me to dive into. Good luck and I do hope there is an easier way...
tom

Get ASAPUtilities from http://www.asap-utilities.com Under "Formula".

Saved me hours with exactly the same dilemma!

Hi maybe the VBA code will help - this is from my old pal Dave Hawley - from his box of tricks

Cheers Dave

Sub MakeAbsoluteorRelative()
'Written by OzGrid Business Applications
'www.ozgrid.com

Dim RdoRange As Range
Dim i As Integer
Dim Reply As String

'Ask whether Relative or Absolute
Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Relative row/Absolute column = 1" & Chr(13) _
& "Absolute row/Relative column = 2" & Chr(13) _
& "Absolute all = 3" & Chr(13) _
& "Relative all = 4", "OzGrid Business Applications")

'They cancelled
If Reply = "" Then Exit Sub

On Error Resume Next
'Set Range variable to formula cells only
Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)

'determine the change type
Select Case Reply
Case 1 'Relative row/Absolute column
'Loop through each area and change to Absolute
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
Next i

Case 2 'Absolute row/Relative column
'Loop through each area and change to Relative
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
Next i

Case 3 'Absolute all
'Loop through each area and change to Relative
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
Next i

Case 3 'Absolute all
'Loop through each area and change to Relative
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = _
Application.ConvertFormula _
(Formula:=RdoRange.Areas(i).Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
Next i


Case Else 'Typo
MsgBox "Change type not recognised!", vbCritical, _
"OzGrid Business Applications"
End Select

'Clear memory
Set RdoRange = Nothing
End Sub

Thanks VoG, the add-ins are really cool.
Dave, thanks for the code, I knew about it and tried to use ConvertFormula method for a whole sheet. For whatever reason I could not use it for xlAbsolute constant, i.e. exactly for what I needed mostly. I have to keep an eye on this one and see if anything in my formulas could have an impact.
This message was edited by Corni on 2002-11-10 14:20

VoG said:

Get ASAPUtilities from ASAP Utilities for Excel - The popular add-in for Excel users. Easy to use tools that save time and speed up your work in Excel. We also offer a free edition. Under "Formula".

Saved me hours with exactly the same dilemma!

Click to expand...

I just downloaded a free trial of asap-utilities.com and can tell already that I will transition to a paid version of the tool. Converted my absolute reference issue in one click (after installation). Today's date is Dec 2021 and the most recent update to the ASAP tool was October, so confirming to readers they are still cranking.

damonsb said:

I just downloaded a free trial of asap-utilities.com and can tell already that I will transition to a paid version of the tool. Converted my absolute reference issue in one click (after installation). Today's date is Dec 2021 and the most recent update to the ASAP tool was October, so confirming to readers they are still cranking.

Click to expand...

And you may be curious about the price: After a 90-day free trial, business users convert at a one-time fee of $49. Students free.

There is a minor typo in the VBA code provided... Here is the VBA code with corrections and tested, it works flawlessly and no need to buy plugins: