Change multiple cell references from relative to absolute ($) at once

Question

I have the following values and formulas in my Excel spreadsheet:

          A                             
1      Shirt         =Sheet2!A2
2        50          =C1
3       350          =Sheet3!D7
4    Product B       =F8
5
6

The values in Column A I get from other parts of the Excel file by using only relative cell references.

Now I want to change those cell references from relative to absolute so they look like this:

          A                             
1      Shirt         =Sheet2!$A$2
2        50          =$C$1
3       350          =Sheet3!$D$7
4    Product B       =$F$8
5
6

Therefore, I tried to go with the VBA from here:

Sub Test()
ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, 1)
End Sub

However, it only works for the active cell but I want that the VBA goes through the entire sheet. Therefore, I was trying something like this:

Sub Test()
Sheet1.Formula = Application.ConvertFormula(Sheet1.Formula, xlA1, xlA1, 1)
End Sub

Now I get the error "Object or method not found". How do I have to change the VBA to make it work?

Maybe `ActiveSheet.Cells.Formula` instead of `Sheet1.Formula` would work? But I'm not pretty sure if all formulas can be converted at once. — Foxfire And Burns And Burns, Jul 04 '18 at 08:35

Axel Richter · Accepted Answer · 2018-07-04T09:06:27.823

3

Changing this at once will not be possible. A kind of loop over the single formula cells is needed.

You can get special cells using Range.SpecialCells Method. Thus you can get all formula cells and then do looping over them and converting them to absolute:

Sub convertAllSheetFormulasToAbsolute()
 Dim oCell As Range
 With ActiveSheet
  For Each oCell In .Cells.SpecialCells(Type:=xlCellTypeFormulas)
   oCell.Formula = Application.ConvertFormula(oCell.Formula, xlA1, xlA1, xlAbsolute)
  Next
 End With
End Sub

edited Jul 04 '18 at 09:06

answered Jul 04 '18 at 08:50

Axel Richter

56,077
6
60
87

Perfect solution. Thanks a lot. – Michi Jul 04 '18 at 09:23

score 3 · Answer 2 · edited Nov 10 '21 at 22:29

If you want to change just the selected cells (not all formulas in the entire sheet), use the following macro:

Sub Formulas_To_AbsoluteReference()
 Dim oCell As Range
 With Selection
  For Each oCell In .Cells.SpecialCells(Type:=xlCellTypeFormulas)
   oCell.Formula = Application.ConvertFormula(oCell.Formula, xlA1, xlA1, xlAbsolute)
  Next
 End With
End Sub

Some similar macros:

Sub Formulas_To_LockRowNumber_RelColumnLetter()
 Dim oCell As Range
 With Selection
  For Each oCell In .Cells.SpecialCells(Type:=xlCellTypeFormulas)
   oCell.Formula = Application.ConvertFormula(oCell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
  Next
 End With
End Sub


Sub FormulasTo_LockColLetter_RelRowNum()
 Dim oCell As Range
 With Selection
  For Each oCell In .Cells.SpecialCells(Type:=xlCellTypeFormulas)
   oCell.Formula = Application.ConvertFormula(oCell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
  Next
 End With
End Sub


Sub FormulasTo_AllRelative_Reference()
 Dim oCell As Range
 With Selection
  For Each oCell In .Cells.SpecialCells(Type:=xlCellTypeFormulas)
   oCell.Formula = Application.ConvertFormula(oCell.Formula, xlA1, xlA1, xlRelative)
  Next
 End With
End Sub

Nice collection - thank you! – Dag Hjermann Nov 09 '21 at 03:51 — Dag Hjermann, Nov 09 '21 at 03:51

score 0 · Answer 3 · answered Mar 07 '19 at 12:53

As an alternative, the ASAP Utilities for Excel add-in provides a menu command that converts your selected range of cells to absolute references, mixed references, or relative references. It's worked flawlessly for me for years.

Once you have the add-in installed, in the Formulas menu, select "Change formula reference style (e.g. A1 to $A1, etc.).

Change multiple cell references from relative to absolute ($) at once

3 Answers3