Macro Code to find and replace text in every worksheet not working

Hi,

what I am trying to do is to write some macro code to 'Find and Replace' certain text strings in each of my work sheet and replace with the correct text e.g. the macro will look in each worksheet in turn. find the text if it exists and replace with new text.

I have tried writing the code below but it appear only to work on the active worksheet

Please Login or Register  to view this content.

Any help would be greatly appreciated

Excel version 2003


Many thanks


Rob

Maybe

Please Login or Register  to view this content.

VBA Noob

VBA Noob

Thanks for the prompt reply BUT.......

I will be searching for numerous text strings (adding to the macro manually) and each find will have a separate replace e.g. I could be looking for oranges and replacing it with lemons or looking for melons and replacing with grapes, looking for bananas and replacing with plums etc etc.

As the macro will have several searches i.e. find and replace and I intend to edit and amend the macro before each time I run it to ensure the text to find and text to replace is correct as this will still save time as I have many work sheets to change.

Unfortunately the macro you kindly wrote finds the different text i.e., "Test Me out", "Test this as well" but only returns the same value of “That Seems To work !!",

You help would be greatly appreciated


Rob

Try

Please Login or Register  to view this content.

VBA Noob

VBA Noob

Thanks that works a treat......BUT....as robert111 says is it possible to have a separate work sheet where you could have a Colum for ‘Find’ and a Colum for ‘Replace’ and let the macro read the table and find and replace automatically.

This would save me having to edit the macro each time especially as would be building the data in the FIND/REPLACE table on a daily/weekly basis.


Thanks dare I say in advance again ?

RB

Hi roberts,

See if attached helps you


Code used

Please Login or Register  to view this content.

VBA Noob

VBA Noob

Excellent - just what the Doctor ordered ! It worked a treat !!


Many thanks again


Regards

BB