Excel - MACRO: Multiple Find / Replace

Asked By mam on 23-Jul-08 09:02 AM
I want a macro that searches a worksheet for specific strings and replaces
these with another.
In detail:
Search on worksheet (whole worksheet, i.e. lots of rows)
Find: "CAR" and replace with "MIS-CAR"
Find: "BAG" and replace with "MIS-BAG"

etc etc

I need about 20 such FIND-REPLACE in this same macro.

Can you help me, since I got no clue of VBA ???

Joe replied on 23-Jul-08 09:22 AM
OldWords = Array("CAR", "BAG")
NewWords = Array("MIS-CAR", "MIS-BAG")
For i = LBound(OldWords) To UBound(OldWords)
Cells.Replace OldWords(i), NewWords(i)
Next i
Bernard Liengme replied on 23-Jul-08 09:23 AM
This might do what you want

Sub MakeChange()
For Each mycell In Selection
Select Case mycell.Value
Case "CAR": mycell.Value = "MIS-CAR"
Case "BAG": mycell.Value = "MIS-BAG"
End Select
End Sub

Select the used range and run the macro
You should be able to extend the code as needed
best wishes
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
Susan replied on 26-Jul-08 12:07 AM
the easiest thing for you to do would be to use (in the toolbar):
Record new macro

it will let you name the macro you are going to record (see note below
about where to store it).
then do exactly what you want to do - edit/replace all of the strings.
then stop the macro recorder.
voila!  you have a macro.
to use it again, go to:

and you will find a list of available macros.  the one you just
recorded will be listed there.  if you choose that macro, it will run
again (but will probably not show it doing anything because you just
did it - it's not going to find any text to replace until you add more

note - if you want this to work on multiple workbooks, then when you
are naming the macro, tell it to save it in the personal.xls.  then it
will be available in every workbook you open.

hope this helps