# Tom’s Tutorials For Excel: Converting All Formula Cell References From Relative to Absolute

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```
###### 14 comments on “Tom’s Tutorials For Excel: Converting All Formula Cell References From Relative to Absolute”
1. Colin Delane says:

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

• Tom Urtis says:

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.

3. Stanley says:

Hi Tom, thank you for the macro! Do you have a macro that reverses the process, changing everything from Absolute to Relative reference?

• Tom Urtis says:

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```

4. Kevin ScoreSelling says:

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.

• Tom Urtis says:

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.

• Al says:

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!

• Tom Urtis says:

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.

• Al says:

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

5. Albert says:

Thank you Tom,

6. Yrida says:

Thank you, Tom!
This worked perfectly and saved hours of tedious work!