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

2. bhaskar says:

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.

5. Albert says:

Thank you Tom,

• Tom Urtis says:

You’re quite welcome!

6. Yrida says:

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

• Tom Urtis says:

Happy to know it worked for you. Thanks!