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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
You could always do a find-replace, and replace all instances of $ with nothing.
 

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
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!
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,942
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
 

Forum statistics

Threads
1,144,391
Messages
5,724,075
Members
422,535
Latest member
navjeet

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
Top