Convert a range of cells formulas from relative to absolute

Corni

Active Member
Joined
Mar 2, 2002
Messages
328
Is any shortcut to convert a range of cells formulas from relative to absolute, without VBA ConvertFormula method, and not with F4.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Corni:

I am not quite sure about your inquiry, however, could you make use of the ADDRESS function ... see the worksheet simulation ...
Book1
ABCD
1A1$A$1
2A$1
3$A1
4A1
Sheet6
</SPAN>

If this is of no use -- my apologies.
 

Corni

Active Member
Joined
Mar 2, 2002
Messages
328
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.
 
L

Legacy 98055

Guest
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
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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
 

Corni

Active Member
Joined
Mar 2, 2002
Messages
328
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,633
Messages
5,626,007
Members
416,151
Latest member
Openminded intellectual

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top