a vb argument that would find and replace w/ multiple values

[jbagdon]

I am needing to do a find and replace on multiple values in an excel worksheet. For example in column d when you find ABC123456 replace with XYZ654321. But there are several hundred in a sequence that need to be replace w/ a different sequence.

Is there something I can do in vb that would help me do this?
For example the argument is:

FIND=XYZ000001
REPL=ABC000123
SFOR=5
OTHR=0

[Leith Ross]

Hello jbagdon,

Welcome to the Forum!

I am not clear on how your data is arranged and what needs replaced. Here is an example macro that will replace all of the "XYZ000001" with "ABC000123" on the active sheet. If you can post it workbook, it would be a big help.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[jbagdon]

I am in over my head because although your answer made some sense I can't even remember where to start doing what you posted. 5 years ago I was much more comfortable w/ macros since then I don't use them so the use or lose it kicked in.

Thank you so much for your reply.

[Leith Ross]

Hello jbagdon,

Here is the procedure for installing and running the macro...

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

[jbagdon]

workbook example.xls
Here is a small example of the workbook.
I have created the macro, thanks for your help.

The issue is when I run it nothing is replaced w/ the new set. I still feel like I'm missing a big step.

[VBA Noob]

See attached example

Change What and Repl values for you're values

Please Login or Register  to view this content.

VBA Noob

[Leith Ross]

Hello jbagdon,

I have installed the macro show below in the attached workbook. To run the macro, just click the "Replace All" button.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[jbagdon]

How do you create such buttons?

[Leith Ross]

Hello jbagdon,

The button I placed on the worksheet is from the Forms toolbar.

Adding Buttons to the Worksheet

1. Click View on the Excel Menu Bar or use ALT+V.
2. Click Toolbars or type T to display the available toolbars.
3. Click on Forms or use the Down Arrow to select this toolbar.
4. Look for the rectangular icon. When you place the mouse on it you will see Button displayed.
5. Click on this icon.
6. Move the cursor to the cell where you want the button. Left Click and Hold the button down while you move the mouse. This will draw the button. Release the button when you are done.
7. A dialog box will appear asking you to assign a macro. Close the dialog. The macro will be added later.
8. Repeat steps from 4 on to add more buttons.

Sincerely,
Leith Ross

[jbagdon]

One last question. If my list of items to be replaced is very long how do I wrap text in the macro?

[Leith Ross]

Hello jbagdon,

The macro was written to replace only one word. If you want to replace multiple words using the macro then it will need to changed to accept a string argument that will be used as the search term. Can you give me an example of what you to do? I can then either modify the macro or provide you with a better solution.

Sincerely,
Leith Ross

[Avens]

I need a macro to do the same thing. I've used the code below, substituting my own values, but am getting a Run-time error 9: Subscript out of range.

The debugger takes me to the line
" Rng.Replace What:=What(i), Replacement:=Repl(i), MatchCase:=False"

Any thoughts?

Thank you!

See attached example

Change What and Repl values for you're values

Please Login or Register  to view this content.

VBA Noob

[HelpExc]

Can I use this code for 400 words what is needed to replace by space? If a copy all those 400 words into "What = Array("")" cell then all those 400 word become red.

See attached example

Change What and Repl values for you're values

Please Login or Register  to view this content.

VBA Noob

[jeffreybrown]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.