# OzGrid

Convert Excel Formulas from Relative to Absolute

Category: [Excel]  Demo Available

# Convert Excel Formulas from Relative to Absolute

## Convert Selected Excel Formulas from Absolute to Relative References or Vice Versa

Got any Excel Questions? Free Excel Help Convert Formulas from Relative to Absolute. Convert Formulas from Absolute to Relative References

The Excel macro code below can be used to convert all Excel formulas from absolute to relative and/or relative to absolute. It can also give a mix of relative row, absolute column reference, or absolute row, relative column reference. Simply select the cells that should be changed, run the code and choose the reference type you want. There are 2 Excel macros below, the first is the fastest but can cause issues with mega formulas and/or array formulas. The second is slower, but less likely to cause issues. Thanks to Andy Pope . As with any code, save you Workbook BEFORE running the code.

TIP: You can toggle through the 4 reference types a formula can use by selecting the cell housing the formula, then click in Formula Bar, then the reference part (e.g A1, \$A\$1 etc) and push F4. Each push of F4 will toggle the reference type.

If you are not familiar with running Excel macro code, follow the steps below:

1) Go to the Visual Basic Editor, Tools>Macro>Visual Basic Editor (Alt+F11).

2) Insert a standard Module, Insert>Module.

3) Copy the code and paste directly in the Module just added.

4) Go back to Excel proper, click the top right X, or push Alt+Q and save!

5) Save you Workbook. Select your range of cells to run the code on.

6) Run the macro, go to Tools>Macro>Macros (Alt+F8) select the macro name and click "Run".

```Sub MakeAbsoluteorRelativeFast()

'www.ozgrid.com

Dim RdoRange As Range

Dim i As Integer

Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _

& "Relative row/Absolute column = 1" & Chr(13) _

& "Absolute row/Relative column = 2" & Chr(13) _

& "Absolute all = 3" & Chr(13) _

& "Relative all = 4", "OzGrid Business Applications")

'They cancelled

If Reply = "" Then Exit Sub

On Error Resume Next

'Set Range variable to formula cells only

Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)

'determine the change type

Case 1 'Relative row/Absolute column

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)

Next i

Case 2 'Absolute row/Relative column

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)

Next i

Case 3 'Absolute all

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

Next i

Case 4 'Relative all

For i = 1 To RdoRange.Areas.Count

RdoRange.Areas(i).Formula = _

Application.ConvertFormula _

(Formula:=RdoRange.Areas(i).Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

Next i

Case Else 'Typo

MsgBox "Change type not recognised!", vbCritical, _

End Select

'Clear memory

Set RdoRange = Nothing

End Sub

```

```Sub MakeAbsoluteorRelativeSlow()

'Written for www.ozgrid.com

'By Andy Pope

'www.andypope.info/

Dim RdoRange As Range, rCell As Range

Dim i As Integer

Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _

& "Relative row/Absolute column = 1" & Chr(13) _

& "Absolute row/Relative column = 2" & Chr(13) _

& "Absolute all = 3" & Chr(13) _

& "Relative all = 4", "OzGrid Business Applications")

'They cancelled

If Reply = "" Then Exit Sub

On Error Resume Next

'Set Range variable to formula cells only

Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)

'determine the change type

Case 1 'Relative row/Absolute column

For Each rCell In RdoRange

If rCell.HasArray Then

If Len(rCell.FormulaArray) < 255 Then

rCell.FormulaArray = _

Application.ConvertFormula _

(Formula:=rCell.FormulaArray, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)

End If

Else

If Len(rCell.Formula) < 255 Then

rCell.Formula = _

Application.ConvertFormula _

(Formula:=rCell.Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)

End If

End If

Next rCell

Case 2 'Absolute row/Relative column

For Each rCell In RdoRange

If rCell.HasArray Then

If Len(rCell.FormulaArray) < 255 Then

rCell.FormulaArray = _

Application.ConvertFormula _

(Formula:=rCell.FormulaArray, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)

End If

Else

If Len(rCell.Formula) < 255 Then

rCell.Formula = _

Application.ConvertFormula _

(Formula:=rCell.Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)

End If

End If

Next rCell

Case 3 'Absolute all

For Each rCell In RdoRange

If rCell.HasArray Then

If Len(rCell.FormulaArray) < 255 Then

rCell.FormulaArray = _

Application.ConvertFormula _

(Formula:=rCell.FormulaArray, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

End If

Else

If Len(rCell.Formula) < 255 Then

rCell.Formula = _

Application.ConvertFormula _

(Formula:=rCell.Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

End If

End If

Next rCell

Case 4 'Relative all

For Each rCell In RdoRange

If rCell.HasArray Then

If Len(rCell.FormulaArray) < 255 Then

rCell.FormulaArray = _

Application.ConvertFormula _

(Formula:=rCell.FormulaArray, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

End If

Else

If Len(rCell.Formula) < 255 Then

rCell.Formula = _

Application.ConvertFormula _

(Formula:=rCell.Formula, _

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

End If

End If

Next rCell

Case Else 'Typo

MsgBox "Change type not recognised!", vbCritical, _

End Select

'Clear memory

Set RdoRange = Nothing

End Sub

```

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

### Gallery

stars (0 Reviews)