Change references

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

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”











About the Author

A co-author of Data Science for Fundraising, an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.

Related Posts

About the Author