Multiple find and replace

[terryhenderson]

Hi Guys,

I have a workbook with 2 worksheets, WS1 and WS2, as per attached example.
WS1 is the sheet that has the words to be found and replaced in WS2. It has two columns: ColA with words to find and ColB with words to be replacing those in ColA.

What I am after is a script to search for each word in ColA of WS1, and find it in WS2, then replace it with its analogous word in ColB of WS1. The result should be in a new worksheet, eg WS3

As per attached example, WS2 is demonstrated before and after applying "find and replace".

Any assistance in this issue is highly appreciated.
Many thanks in advance.Multi_find_replace_example.xlsm

[jindon]

Try

Please Login or Register  to view this content.

Multi_find_replace_example with code.xlsm

[terryhenderson]

That is extraordinary Jindon, thank you very much indeed.
Just one more request, can we expand the range instead of just ColD, can it be for the entire 8 columns (A-H)?
Very much appreciated.

[jindon]

Just change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[terryhenderson]

Thanks very much Jindon. All the best.

[jindon]

If that takes care of your original question, select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.