Replace text within a formula?

[EconSean]

Greetings all.

I have multiple (20) worksheets, each with approximately 50 formulas. The formulas are all the same currently, in that they reference a particular sheet, call it Region 1. Now, I would like to be able to change this to Region 2, Region 3, etc, depending upon the worksheet that the formula is on.

I am unable to get the standard Edit - Replace to work with the formulas, as I get an "Unable to find match" message.

Anyone have any experience with this?

Thanks in advance for any help.

Kind Regards,

Sean

 

[Tom Urtis]

One way to go about that is, first, press Ctrl+Tilde key (the one above the Tab key and to the left of the numeral 1 key on most keyboards). That will display all your formulas. Next, select the range whose formulas you want to change. Click on Edit > Replace, type in Region 1 in Find what; and Region 2 or whatever in Replace with, click Replace All and that should do it.

Did you need a macro to do that? Post back if so.

 

[PaddyD]

...or edit | go to | special | formulas to do the selection.

 

[EconSean]

Thanks Tom. I had no idea about Cntr-Tilde. I will check this out right now. If this works (and I have no reason to believe that it won't), then I don't need a macro.

Thanks again.

Regards,

Sean C.

 

[Tom Urtis]

Hi Paddy...I thought about that method of selection, which selects all formulas on the sheet, but suggested Ctrl+Tilde because it gives more flexibility as there might be a lot of formulas with Region 1 that might not be intended for a change.

I got in trouble one day with a client because they only wanted to change a few formulas on a sheetful so I've become more conservative in my old age I guess.

Thanks for the reminder.

 

[PaddyD]

I got in trouble one day

Bad Tom...

 

[PaddyD]

By the way, the red circle around the exclamation mark in EconSean's original post is just me trying out the 'report post' function...

 

[Tom Urtis]

Hey Paddy, what red circle? I don't see one.

OK dumb question, what's a report post function?

 

[EconSean]

Tom and Paddy,

I was still unable to get the Replace function to work; I was still being told that a match couldn't be found.

Tom, I came across this code from you, and with some modifications made it do what I need. I have to change Region 1 to Region x, in the code, manually select the range, and then run the macro, but this is MUCH quicker than having to go cell by cell to change the formulae.

Thanks again for your suggestions.

Regards,

Sean C.

Public Sub ChangeFormula()

On Error Resume Next
Selection.SpecialCells(xlCellTypeFormulas).Replace What:="1'", Replacement:="9'", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
If Err.Number <> 0 Then
MsgBox "No data found"
End If
On Error GoTo 0

End Sub

 

[Tom Urtis]

Strange...I tested that first idea before posting it, so I wonder why it did not work for you the same way it did for me. I'm using XL2002 on XP so I don't know if it's a version issue.

So are you all set or do you still need assistance with this? Post back if there's an outstanding problem.