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: , , , , , , , , , , , ,
19 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

      • Sara says:

        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!

        • Tom Urtis says:

          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.

  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!

  7. Marcos says:

    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

    • Tom Urtis says:

      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.

  8. fatih ANIL says:

    Tom’s code run in a related manner perfectly on my worksheet. Thank you.

Leave a Reply

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

*