Skip to content

Multiple find and replace macro for Excel

This is a simple macro to perform a bulk find and replace operation in Excel. Search will be performed on a range that you define and replacement will be based on pairs that you provide in a separate range.

Before starting, define your find and replace pairs, say on columns D and E.

Sub multiFindandReplace()

Dim myList, myRange
Set myList = Sheets(“sheet1”).Range(“D1:E11”) ‘two column range with find/replace pairs
Set myRange = Sheets(“sheet1”).Range(“B1:B99”) ‘range to be searched and replace
For Each cel In myList.Columns(1).Cells
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
Next cel

End Sub

  • LookAt:=xlWhole causes the search to be performed on entire cell contents. If you want to allow partial find and replace, remove that attribute including the preceding comma.
  • myList defines your values for find and replace. In the example above, search values are in D1:D11 and matching replace values are in E1:E11
  • myRange defines the cells to be searched/replace. Example above searches B11:B99 and performs the replace.

 

If you want to perform the search/replace on all sheets of an open document, try the following:

Sub multiFindandReplace()

Dim myList, myRange
Set myList = Sheets(“sheet1”).Range(“A1:B1”) ‘two column range with find/replace pairs

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

Set myRange = ws.Range(“B1:B99”) ‘range to be searched and replace
For Each cel In myList.Columns(1).Cells
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
Next cel
Next ws

End Sub

 

Categories: Localization engineering.

Comment Feed

14 Responses

  1. Hi,

    Your code appears to be exactly what I’m looking for however i get a type mismatch error that I’m not sure how to resolve? I am trying to work with strings.

    Can you help?

    Thanks,

    Peter

  2. Hi Emre,
    Thanks for the code. However, it seems to update the text in all worksheets (including the reference table). I just want it to find and replace within one worksheet …
    Thoughts?

  3. Bilal, this will make the replacement in the cells you define in “Set myRange = Sheets(“sheet1″).Range(“B1:B99″)”

    Change the range to the sheet name you want to process and the cell range and that should take care of it

    • Hi..
      i feel like a moron because i just can’t figure this out.. i created a macro to try to do what you are describing. (it didn’t work) – so i edited my macro with your code (as shown below).. and it doesn’t seem to do anything. i have a string of text in column A. part of that text also appears in column B (an english phrase). i want to replace the text that is in column A with text that is in column C (a translation of the phrase that is only part of column A.) – does that make sense?

      Sub ReplaceText()

      Dim myList, myRange

      Set myList = Sheets(“Portuguese”).Range(“B4:C11”)
      Set myRange = Sheets(“Portuguese”).Range(“A4:A11”)

      For Each cel In myList.Columns(1).Cells
      myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value

      Next cel

      End Sub

      • Tony, not sure this will fix your problem but try adding LookAt:=xlWhole
        after
        myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value

        So that line reads
        myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole

  4. @Emre : Thanks! Actually this code works properly *only* with your suggested modification re: LookAt:=xlWhole

    I have tried this code without this modification, and for some reason it only provides messed up results in large ranges (long columns).

    • Abramo, correct, that is necessary. Note that it is included in the original code segment posted in the entry.

  5. I have been trying you’re macro but I can’t get it to work for what I want. Lets say I have ABCD in a Cel, in my pairins I mark that I want to replace ‘A’ with 1. Even after removing the xlWhole part, it still will not replace the ‘A’ to a 1 and have it 1BCD. Any way to make it do a true find and replace without the whole cell thing?

    • Actually using xlPart works very well as it will search a string for anything. Thanks for the macro!

  6. when i reun this macro code it is showing compilation error and syntax error error in line –Set myList = Sheets(“sheet1″).Range(“D1:E11”) ‘two column range with find/replace pairs.

    i gave values to be searched in Col B and look up values in Col D and replace values in Col E. keeping col A and col C blank..pls help. Thanks

  7. Thanks for the macro. With some modifications for my sheets, it works great!

  8. Hello Emre and friends,I have 72 worksheets and I want to replace the same set of values in Range(“D1:E11″) in all the tabs for their respective ranges i.e (“B1:B99″) . So how do I change the code where it states Sheets(“sheet1″) – – – so that it reflects all the tabs ? (I guess when I tried this above code,it only replaces whats on my active sheet,whereas I want it to do replacements for the whole excel)Thanks.I am new to VBA. Plz bear with me.

    • Sam, you will have to loop through all sheets to do a search replace on all sheets, I just added it to the main post since the format doesn’t look the best in the comments.



Some HTML is OK

or, reply to this post via trackback.