Let’s say you have an excel worksheet which contains a lot of formulas and you are asked to change reference of formulas from relative to absolute. This seems to be an easy task at first sight but you’d realise it’s not straightforward when your formulas are not in the same column or row. In short you can’t just change reference of formula in one cell and drag down to make it work to the other remaining cells.

Absolute reference is used by including dollar sign ($) before row or column (or both) in a formula. For example $B$2, B$2, $B2. The purpose of it is when you do not want a cell reference to change when dragging or copying formula. Whereas Relative reference is used when you want formula to automatically change cell reference when copy to another cell.

This post covers the excel macro for conversion of reference in the formula for the following:

Relative row and Absolute column Absolute row and Relative column Absolute both row and column Relative both row and column

Sub Convert()

Dim myRange As Range

Dim i As Integer

Dim response As String

‘Check user response

response = InputBox(“Change formulas to?” & Chr(13) & Chr(13) _

& “Relative row/Absolute column = Type 1” & Chr(13) _

& “Absolute row/Relative column = Type 2” & Chr(13) _

& “Absolute all = Type 3” & Chr(13) _

& “Relative all = Type 4″, ” “)

If response = “” Then Exit Sub

On Error Resume Next

‘Set Range variable to formula cells only

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

‘Determine the change type

Select Case response

Case 1 ‘Relative row/Absolute column

For i = 1 To myRange.Areas.Count

myRange.Areas(i).Formula = _

Application.ConvertFormula _

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

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)

Next i

Case 2 ‘Absolute row/Relative column

For i = 1 To myRange.Areas.Count

myRange.Areas(i).Formula = _

Application.ConvertFormula _

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

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)

Next i

Case 3 ‘Absolute all

For i = 1 To myRange.Areas.Count

myRange.Areas(i).Formula = _

Application.ConvertFormula _

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

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

Next i

Case 4 ‘Relative all

For i = 1 To myRange.Areas.Count

myRange.Areas(i).Formula = _

Application.ConvertFormula _

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

FromReferenceStyle:=xlA1, _

ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

Next i

Case Else ‘Typo

MsgBox “Type values between 1 and 4”, vbCritical, _

” “

End Select

‘Clear memory

Set myRange = Nothing

End Sub

Open Excel WorkbookPress ALT + F11 shortcut key to open visual basic editor (VBE)To insert a module, go to Insert > ModulePaste the above VBA code in the modulePress ALT + F8 shortcut key to run macro Macro will return a message box for user input. In the message box, type 1 if you want formula to be changed to “Relative row and Absolute column”. Type 2 for “Absolute row and Relative column”. Type 3 for “Absolute All”. Type 4 for “Relative All”

By default the macro applies conversion in all the formulas present in the active worksheet. Incase you want macro to do conversion in some specific cells, you can do it by selecting those cells before running the macro

Read MoreListenData