Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Replace text within a formula?

This is a discussion on Replace text within a formula? within the Excel Questions forums, part of the Question Forums category; Greetings all. I have multiple (20) worksheets, each with approximately 50 formulas. The formulas are all the same currently, in ...

  1. #1
    Board Regular EconSean's Avatar
    Join Date
    Apr 2002
    Location
    Philadelphia, PA
    Posts
    129

    Default Replace text within a formula?

    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

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,006

    Default

    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.

  3. #3
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

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

  4. #4
    Board Regular EconSean's Avatar
    Join Date
    Apr 2002
    Location
    Philadelphia, PA
    Posts
    129

    Default

    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.

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,006

    Default

    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.

  6. #6
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    I got in trouble one day
    Bad Tom...

  7. #7
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

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

  8. #8
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,006

    Default

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

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

  9. #9
    Board Regular EconSean's Avatar
    Join Date
    Apr 2002
    Location
    Philadelphia, PA
    Posts
    129

    Default

    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

  10. #10
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,006

    Default

    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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com