MACRO: Multiple Find / Replace

[mami]

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"
AND
Find: "BAG" and replace with "MIS-BAG"
AND

etc etc

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

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

 

[Joel]

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]

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
Next
End Sub

Select the used range and run the macro
You should be able to extend the code as needed
best wishes

 

[Susan]

the easiest thing for you to do would be to use (in the toolbar):
Tools
Macro
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:
Tools
Macro
Macros

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
text).

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
susan