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
