Make all references floating

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
Hi all-

I am updating a file from a few years ago, and for whatever reason, it is designed using only E$4 style references.

I would like to have a macro that changed all occurances of E$4, $E4, $E$4 to E4, for example. There's a ton of them on the page, so I don't really want to manually do the updating.

Clues? Assistance? Working Macro?

any or all of those would be appreciated.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
You could always do a find-replace, and replace all instances of $ with nothing.

I just thought of that before checking the forum again, and thought... man, they're going to think I'm dumb.

Thanks for the response!
 
Upvote 0
Here's a macro I use:

Code:
Sub ChangeReferences()
' This macro allows user to change the cell references in all selected cells
' containing formulae.

Dim intResponse as Integer, i as Integer, rngMyRange as Range

    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells.", vbInformation + vbOKOnly, "Invalid Range Selection"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    On Error Resume Next
    Set rngMyRange = Selection.SpecialCells(Type:=xlFormulas)
    If Err <> 0 Then
        MsgBox "No formulae were found in selected cells", vbInformation + vbOKOnly
        On Error GoTo 0
        Exit Sub
    End If
    intResponse = Application.InputBox("Enter a number" & vbCrLf & "1 = Absolute References ($A$1)" & vbCrLf & "2 = Column Relative/Row Absolute (A$1)" & vbCrLf & "3 = Column Absolute/Row Relative ($A1)" & vbCrLf & "4 = Relative References (A1)", "Change Cell References", 1, , , , , 1)
    If intResponse = 1 Or intResponse = 2 Or intResponse = 3 Or intResponse = 4 Then
        For i = 1 To rngMyRange.Areas.count
            rngMyRange.Areas(i).Formula = Application.ConvertFormula(rngMyRange.Areas(i).Formula, xlA1, xlA1, intResponse)
        Next i
        'rngMyRange.Areas(i).Formula = Application.ConvertFormula(
    End If
exitsub:
    On Local Error GoTo 0
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top