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..

Sub find_and_replace()
    Selection.Replace What:=Range("A1"), Replacement:=Range("A2"), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
End Sub

**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.