Convert a range of cells formulas from relative to absolute

C

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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:

VBA Code: 
Sub MakeAbsoluteOrRelative()

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 4 'Relative 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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Changing multiple cell references to absolute references within formula
Replies
1
Views
594
JWill78
J
sparky2205
  • Solved
Absolute reference from a lookup
2
Replies
17
Views
709
Alex Blakenburg
A
T
  • Question Question
Referencing - Relative or Absolute.
Replies
9
Views
644
MARK858
MARK858
M
  • Solved
Absolute Reference - Change absolute range without manual intervention
Replies
7
Views
490
Peter_SSs
Peter_SSs
gtd526
  • Question Question
R1C1 Row to Absolute
2
Replies
17
Views
792
gtd526
gtd526

Forum statistics

Threads
1,223,882
Messages
6,175,164
Members
452,615
Latest member
bogeys2birdies

Share this page

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
Back
Top