Excel macro to make selected range absolute or relative reference

Question

Accordingly, I recently came across a macro that would let you make the selected cells' absolute or relative references (would offer you to choose from four alternatives), then update the data as necessary. Since the code was based on the active/selected cells, you could input the names of the workbook or sheet without having to change anything. I looked for it again but was unable to locate it. Any thoughts?

Answer 1

Try  this:

Sub ConverReferenceType()
    On Error Resume Next
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to covert reference type:", "ConvertReferenceType", myRange.Address, Type:=8)
    Set myRange = myRange.SpecialCells(xlCellTypeFormulas)
     myIndex = Application.InputBox("Select a reference type from below list:" & Chr(13) & Chr(13) _
    & "Absolute = 1" & Chr(13) _
    & "Row absolute = 2" & Chr(13) _
    & "Column absolute = 3" & Chr(13) _
    & "Relative = 4", "ConvertReferenceType", 1, Type:=1)
    For Each R In myRange
       R.Formula = Application.ConvertFormula(R.Formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlA1, myIndex)
    Next
End Sub

---

If you don't want the reference box to appear you can comment out this line:

'Set myRange = Application.InputBox("Select one Range that you want to covert reference type:", "ConvertReferenceType", myRange.Address, Type:=8)