Written by If you want to change references of a formula, you could toggle F4 key inside the formula to change the references from Absolute, Column Absolute Row Absolute or all relative.
Although, if you have to change references from a range, you can use these two options
1. Download ASAP utilities, because it’s worth it, they have more than 300 utilities which are used many times.
2. Use this code.
Sub Change_refs()
‘This sub will change references of formula to different options shown by a userform
‘This code will not run without the userform
On Error GoTo Change_refs_Error
Load frmRefType
frmRefType.Show
Dim IPFormula As String, OPFormula As String, c As Range
Dim RefStyle As XlReferenceStyle
With frmRefType
If .obAbs Then
RefStyle = xlAbsolute
ElseIf .obCAbs Then
RefStyle = xlRelRowAbsColumn
ElseIf .obRAbs Then
RefStyle = xlAbsRowRelColumn
ElseIf .obRel Then
RefStyle = xlRelative
Else
Exit Sub
End If
End With
Application.ScreenUpdating = False
For Each c In Selection
If IsEmpty(c) Then GoTo Nextc
IPFormula = c.Formula
OPFormula = Application.ConvertFormula( _
Formula:=IPFormula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, ToAbsolute:=RefStyle)
c.Formula = OPFormula
Nextc:
Next c
Application.ScreenUpdating = True
Unload frmRefType
On Error GoTo 0
Exit Sub
Change_refs_Error:
If Not Application.ScreenUpdating Then Application.ScreenUpdating = True
Unload frmRefType
MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure Change_refs”
End Sub
‘This sub will change references of formula to different options shown by a userform
‘This code will not run without the userform
On Error GoTo Change_refs_Error
Load frmRefType
frmRefType.Show
Dim IPFormula As String, OPFormula As String, c As Range
Dim RefStyle As XlReferenceStyle
With frmRefType
If .obAbs Then
RefStyle = xlAbsolute
ElseIf .obCAbs Then
RefStyle = xlRelRowAbsColumn
ElseIf .obRAbs Then
RefStyle = xlAbsRowRelColumn
ElseIf .obRel Then
RefStyle = xlRelative
Else
Exit Sub
End If
End With
Application.ScreenUpdating = False
For Each c In Selection
If IsEmpty(c) Then GoTo Nextc
IPFormula = c.Formula
OPFormula = Application.ConvertFormula( _
Formula:=IPFormula, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlA1, ToAbsolute:=RefStyle)
c.Formula = OPFormula
Nextc:
Next c
Application.ScreenUpdating = True
Unload frmRefType
On Error GoTo 0
Exit Sub
Change_refs_Error:
If Not Application.ScreenUpdating Then Application.ScreenUpdating = True
Unload frmRefType
MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure Change_refs”
End Sub
You’ll have to download the add-in to make this code run. You can edit the Addin just by opening it in VBA. Here is the Addin. If you install this add in it will create a button “Change References”
Related posts:
hi, I’m trying to download your addin, but it’s not working. could you help, please?
thanks.
Right click and save as this file and from Excel open it thru Tools>Add-Ins