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
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
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,

  6. Yrida says:

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*