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?

Answer 1

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