# Solved: Macro Multiple Find and Replace in Excel 2007



## Lynchie (Sep 9, 2009)

Hi,

Can someone please help? I need a way to be able to find and replace in Excel on a mass scale. I have three colums A, B, C. I want to be able to find the value from Column A and replace it with the value in the same row in column B wherever it appears in Column C

Column A ********Column B ******** Column C
Cat ************Dog************* www.lynchie.com/cat/UK
etc etc**************************www.lynchie.com/horse/uk
*******************************www.lynchie.com/cat/uk
*******************************www.lynchie.com/mouse/uk

(Apologies for the bad rendering of an Excel Sheet)

This is a simple find and replace. However, I have approximately 1,000 pairs in column A and B which need to be found and replaced in Column C (and Column C is 100,000 rows+).

Is there any macro / process which I could use or I'm I doomed to do a find and replace 1,000 times?

Any help / suggestions would be hugely appreciated,

Thanks

Lynchie


----------



## terabytecomputer (Apr 20, 2009)

I'm not sure if you have it worded backwards, or if I'm thinking backwards. What you say you're trying to do doesn't make sense to me.

If you have this:
Column A ********Column B ******** Column C
Cat ************Dog************* www.lynchie.com/cat/UK
Mouse**********Horse************www.lynchie.com/horse/uk
Lion************Cat**************www.lynchie.com/cat/uk
Mouse**********Elephant**********www.lynchie.com/mouse/uk

Do you want to end up with this (what it appears to me you're asking):
Column A ********Column B ******** Column C
Dog ************Dog************* www.lynchie.com/cat/UK
Mouse**********Horse************www.lynchie.com/horse/uk
Lion************Cat**************www.lynchie.com/cat/uk
Elephant**********Elephant**********www.lynchie.com/mouse/uk

Or this (what I think you mean):
Column A ********Column B ******** Column C
Cat ************Cat************* www.lynchie.com/cat/UK
Mouse**********Horse*************www.lynchie.com/horse/uk
Lion************Cat**************www.lynchie.com/cat/uk
Mouse**********Mouse************www.lynchie.com/mouse/uk

or neither?


----------



## Lynchie (Sep 9, 2009)

errr... neither

Apologies - I did not depict this as clearly as I thought. There is no relationship between Column C and the other two columns.

A better way of depicting this would be:

Column A*****Column B
Dog*********Cat
Duck********Horse
Lion*********Fox
Mouse*******Cow

Column C
www.lynchie.com/duck/uk
www.lynchie.com/dog/uk
www.lynchie.com/mouse/uk
www.lynchie.com/lion/uk

Running through. Wherever the term 'dog' appears in Column C I want to replace it with the term 'cat'. Whenever the term 'duck' appears I want to replace it with 'horse'. This means the above would end up as:

Column A*****Column B
Dog*********Cat
Duck********Horse
Lion*********Fox
Mouse*******Cow

Column C
www.lynchie.com/horse/uk
www.lynchie.com/cat/uk
www.lynchie.com/cow/uk
www.lynchie.com/fox/uk

>> terabycomputer - does this make some more sense now?

Best Wishes,

Lynchie


----------



## Rollin_Again (Sep 4, 2003)

Here you go. Just replace the portion in red to reflect the correct range of data in column A.


```
Sub ReplaceText()

For Each vCell In Range("[COLOR="Red"]A2:A100[/COLOR]").Cells

Columns("C:C").Cells.Replace What:=vCell, Replacement:=vCell.Offset(0, 1).Value, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Next vCell

End Sub
```
Regards,
Rollin


----------



## Lynchie (Sep 9, 2009)

Thanks Rollin - works like a dream!

Best Wishes,

Lynchie


----------



## DMR1712 (Sep 24, 2009)

This is exactly what I was looking for, thank you very, very much!

I however have another problem, My column A consists of entries that are formatted like this:

Example 1--------replace by "one"
Example 2--------replace by "two"
etc
Example 10-------replace by "ten"
etc
etc into the hundreds

Maybe you already see my problem, Changing Example 1 ofcourse also changes Example 10. This then becomes "one0" instead of "ten"

Is there a possibility to circumvent this? Maybe reverse the process (instead of top to bottom, bottom to top)?

Thanks in advance for your help.

Best, 
DMR1712


----------



## Rollin_Again (Sep 4, 2003)

Change the portion of the code that is highlighted in red so that is reads *xlWhole*



> Columns("C:C").Cells.Replace What:=Range("A" & i).Value, Replacement:=Range("A" & i).Offset(0, 1).Value, LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> ReplaceFormat:=False


I've also included macro code that will search from the bottom up. Just change the values in red to reflect your correct start and end rows.


```
Sub ReplaceString()

StartRow = [COLOR="Red"]1[/COLOR]
EndRow = [COLOR="red"]10[/COLOR]

For i = EndRow To StartRow Step -1

Columns("C:C").Cells.Replace What:=Range("A" & i).Value, Replacement:=Range("A" & i).Offset(0, 1).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next i

End Sub
```
Regards,
Rollin


----------



## DMR1712 (Sep 24, 2009)

Brilliant! this worked like a charm!

thank you so much!


----------

