MACRO: Multiple Find / Replace
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 |
MACRO: Multiple Find / Replace
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 "mami" wrote: 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 |
MACRO: Multiple Find / Replace
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mami" wrote in message ... 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 |
MACRO: Multiple Find / Replace
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 On Jul 23, 9:02*am, mami wrote: 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 |
All times are GMT +1. The time now is 04:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com