VBA : Change formulas reference

Deepanshu Bhalla Add Comment ,

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.
Excel macro to change formula reference

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

  1. Relative row and Absolute column
  2. Absolute row and Relative column
  3. Absolute both row and column
  4. Relative both row and column
VBA Code

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
How to use VBA Code
  1. Open Excel Workbook
  2. Press ALT + F11 shortcut key to open visual basic editor (VBE)
  3. To insert a module, go to Insert > Module
  4. Paste the above VBA code in the module
  5. Press 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"
Important Points

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

Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 0 Response to "VBA : Change formulas reference"
Next → ← Prev