CODEWITHSUNDEEP
excelvba
Michi
Michi

Reputation: 5471

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

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?

Upvotes: 2

Views: 5298

Answers (3)

RWB
RWB

Reputation: 55

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

Upvotes: 3

Aran
Aran

Reputation: 145

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.).

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61915

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

Upvotes: 3

Related Questions