Find and replace with contents of a cell

**gettingthere** · 01-27-2009, 07:49 AM

I want to replace a text string with constantly changing values. To do this I'd like to link a cell in the Find and Replace tool. Is this possible? Or can this be done through a macro?

**johncassell** · 01-27-2009, 10:33 AM

Can you provide further info. Maybe a short example..

How will Excel know what string to look for and what to replace it with?

**Shijesh Kumar** · 01-27-2009, 10:38 AM

The below code find the text in cell A1 and Replaces with the text in Cell A2

First select the range where u want find and replace and then run the macros..

Please Login or Register  to view this content.

**gettingthere** · 01-27-2009, 02:24 PM

Shijesh. Thanks. I think this is exactly what I'm looking for.

however, I have never done macros before, so if its not too much trouble, can you please guide me step by step what I need to do?

Sorry to be a pain, and thanks.

**Shijesh Kumar** · 01-27-2009, 02:30 PM

sure..

Press Alt + F11 ( A white window will open ).
Click on INSERT (MENU) ---> Module...

Copy and paste the above code in white section..

now come to excel spread sheet
Click on Tool --> Macro --> Macros ( I will open a window )

Select find_and_replace and click on run

**gettingthere** · 01-27-2009, 02:50 PM

Shijesh. Spot on! Thanks. Can I ask a couple of questions though, please?

1.If there were upto 4 strings that needed to be replaced at a time, i.e., for eg find A1 & replace with A2, find B1 & replace with B2, etc. how can that be done

2. If the data was on sheet1, can the macro read the find and replace cells from sheet 2?

3. For the data to be reverted back to its original, is there an easier way that reversing the contents of cell A1 and A2 and then running the macro again?

Thanks again, mate.

**Shijesh Kumar** · 01-27-2009, 03:24 PM

Originally Posted by gettingthere

1.If there were upto 4 strings that needed to be replaced at a time, i.e., for eg find A1 & replace with A2, find B1 & replace with B2, etc. how can that be done

Yes that can be done.. below is the code for that

Please Login or Register  to view this content.

2. If the data was on sheet1, can the macro read the find and replace cells from sheet 2?

Yes this too is possible. Below is the sample code...

Please Login or Register  to view this content.

3. For the data to be reverted back to its original, is there an easier way that reversing the contents of cell A1 and A2 and then running the macro again?

Thanks again, mate.

Changes done by macros is generally permanent .. so u cannot use ctrl+z to revert the changes... u can to run macros again...