Tom’s Tutorials For Excel: Converting All Formula Cell References From Relative to Absolute
When you have a lot of formulas on a worksheet for which you want to convert all cell and range references from relative to absolute, this macro can do the job:
Sub ConvertRelativeToAbsolute() Dim cell As Range, strFormulaOld$, strFormulaNew$ For Each cell In Cells.SpecialCells(3) strFormulaOld = cell.Formula strFormulaNew = _ Application.ConvertFormula _ (Formula:=strFormulaOld, fromReferenceStyle:=xlA1, _ toReferenceStyle:=xlA1, toAbsolute:=xlAbsolute) cell.Formula = strFormulaNew Next cell End Sub
I wrote the following code to allow the user to convert the existing referencing of each cell formula to one of the four possibilities. It is not as elegant/efficient/succinct as Tom’s but offers more flexibility.
Sub ConvertCellReferences()
‘ Amended 22/01/2012
‘ By using the ConvertFormula method available in VBA, you can easily convert
‘ a formula from relative to absolute addressing, and vice-versa. The following short
‘ macro uses this method to change the addressing method used in a range of VISIBLE cells.
‘ The key to how this macro works is in the ConvertFormula method.
‘ The last parameter used by the method is the variable p (in this case = xlAbsolute)
Dim c As Range
Dim p As String
Dim Prompt As String
Dim UserResp As String
Dim UR As Single
Dim CalcMode As XlCalculation
Prompt = “Within all formulae in the visible cells selected:” + vbCrLf
Prompt = “1. Make all cell references (both column and row) ABSOLUTE.” + vbCrLf
Prompt = Prompt + “2. Make all cell references (both column and row) RELATIVE.” + vbCrLf
Prompt = Prompt + “3. Make all COLUMN references ABSOLUTE, and all ROW references RELATIVE.” + vbCrLf
Prompt = Prompt + “4. Make all COLUMN references RELATIVE, and all ROW references ABSOLUTE.”
‘ Capture current calculation setting
CalcMode = Application.Calculation
‘ Switch calculation setting to Manual
Application.Calculation = xlCalculationManual
UR = 0
UserResp = InputBox(Prompt, “Select the type of cell referencing required”)
UR = Val(UserResp)
While UserResp = “” Or UR 5
Exit Sub
Wend
Select Case UR
Case 1
p = xlAbsolute
Case 2
p = xlRelative
Case 3
p = xlRelRowAbsColumn
Case 4
p = xlAbsRowRelColumn
End Select
For Each c In Selection.SpecialCells(xlCellTypeVisible)
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, p)
End If
Next c
‘ Restore calculation setting
Application.Calculation = CalcMode
MsgBox “Converting cell references in selected formula now completed.”
End Sub
some error in lines
Quote
For Each cell In Cells.SpecialCells(3)
strFormulaOld = cell.Formula
strFormulaNew = _
Application.ConvertFormula _
(Formula:=strFormulaOld, fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, toAbsolute:=xlAbsolute)
unquote
it is showing error while running macro
You did not say what kind of error you are getting, but if there are no formulas on the worksheet, that could be the reason why.
Try inserting a line of code like this at the beginning of the macro:
If cells.specialcells(3).count = 0 then exit sub
Otherwise, the code I posted does not error for me, I tested it many times without a problem.
Hi Tom, thank you for the macro! Do you have a macro that reverses the process, changing everything from Absolute to Relative reference?
Try this:
Sub AbsoluteToRelative()Dim cell As Range
For Each cell In activesheet.UsedRange
If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, xlA1, xlRelative)
Next cell
End Sub
I ran into the mismatch error when trying the original code, but not with this one. So I used
Sub RelativeToAbsolute()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, xlA1, xlAbsolute)
Next cell
End Sub
To change all the references to absolute.
However, some of my cells returned a #VALUE! error. After running these two macros. Any reason why that might be?
Thanks in advance!
Hard to say without seeing your workbook, but I suspect the mismatch error is related to the #VALUE! error because they both essentially suggest an attempt at converting a formula when maybe there was not a formula in a cell. Or maybe a UDF, or something that is going on in the cell because the operation should work on all bona fide formula cells.
I ran both posted macros here.
1. Sub AbsoluteToRelative() worked fine.
2. I could not get Sub ConvertRelativeToAbsolute() to run without error;
“Run-time error ’13’:
Type mismatch”
3. I inserted
If cells.specialcells(3).count = 0 then exit sub
Same error.
Hello Kevin – –
I just reran those macros now and they worked as good and error-free tonight as they did when I originally posted this example. I cannot re-create your error, number 13 which is a type mismatch error. This indicates that ***maybe*** (I am only guessing) you have text or something in the cells of your used range that mimic a formula but really not a formula. I tried getting a runtime 13 just now putting errors and text and different data in cells, but no errors running either macro.
Hi Tom, the error Kevin refers to appears if the formula contains quote marks, e.g. =IF(LEN(Data!$A$2)<2,"Check first name",""). is there any way round this?
Many thanks!
Thanks, Al.
I just tried the code again, naming a sheet Data and on another sheet entering some formulas with a relative reference, including the exact one you posted. No problems.
Sometimes those double quote characters need to be manually entered or re-entered, if the formula by chance was copied off the web. There are two kinds of double quote characters. Even if the “good” (ascii 34) double quote characters are entered but posted someplace on line, html sometimes shows or converts them into the “bad” (ascii 147-148) double quote characters that need to be re-entered from the keyboard.
If that is no help, I’m out of ideas because I cannot duplicate this problem. Maybe someone else reading this has had it happen to them before and found a solution.
thanks for trying Tom – I typed the formulae in myself so all a bit of a mystery! Very useful macro for other use cases though
Thank you Tom,
You’re quite welcome!
Thank you, Tom!
This worked perfectly and saved hours of tedious work!
Happy to know it worked for you. Thanks!
Thanks Tom,
Really happy to have found this.
But there seems to be an issue when there are numerous cell refs in a formula – stops working and replaces the formula with #VALUE
eg =IF(C18>0,(52*(1-INPUTS!D$20)*(INPUTS!D$19+INPUTS!D$19*($A18-INPUTS!D$3)*(INPUTS!D$22))),0)
Can you suggest a fix?
I have about 3600 cells that I need to transpose, and absolute refs is the only way that seems to work, as many of the cells reference other worksheets AND cells within the array.
The macro works fine on some cells, but not others
Thanks, Marcos.
My first thought, not seeing your workbook, is that the mix of relative references as you show them are part of calculations that, when operating on other of the 3600 cells and ranges, would result in the #VALUE! error, because some references are changing (or not changing) after the presumed copy. I guess my question is why you would want to make all cell references absolute (or relative if that is your objective), when the nature of your operation is to calculate ranges that are to be referenced partly relative and partly absolute.
Tom’s code run in a related manner perfectly on my worksheet. Thank you.