Find and Replace

[EXLNeophyte]

Greetings,

I have a project and need some help with a find and replace macro.
have seen similar posts; however, I do not understand the language an
or syntax so I need more direct assistance.

I have a multi-page workbook. Sheet 1 column A contains a list o
names, positions, addresses, etc. I would like to search Sheet 2, Shee
3… column D for each occurrence in Sheet 1 column A. If a match i
found then replace it with the contents of Sheet 1 column B same ro
that the matched word is found.
For Example:
Sheet 1 Column A, row 2 <dog>….Sheet 2 Column D, row 10 finds <dog>
then goes back to Sheet 1 Column B row 2 reads <cat> and replaces <cat
for <dog> on Sheet 2 Column D, row 10. The find and replace macr
continues until all words on Sheet 1 Column A have been searched...ther
are more than 750 user entries containing letters and numbers.

Any help would be greatly appreciated

 

[Gestas]

Hi, try pasting this into a module and running. This will search i
column d in each sheet other than sheet1 for each value in sheet1 colum
a (starting with A1) and replace with corresponding value in sheet
column b.

It may be quite slow to run, if so you can replace Range("d:d") wit
something like Range("d1:d100") or what ever row number is appropriat
to speed it up..

Sub FindReplace(

Dim rCell As Rang
Dim sFind As Strin
Dim sReplace As Strin
Sheets("Sheet1").Selec
Range("A1").Selec

Do Until ActiveCell.Value = "
sFind = ActiveCell.Valu
sReplace = ActiveCell.Offset(0, 1).Valu

For Each sht In Worksheet
If sht.Name <> "Sheet1" The

For Each rCell In sht.Range("d:d").Cell
If rCell.Value = sFind The
rCell.Value = sReplac
End I
Next rCel

End I

Next sh
ActiveCell.Offset(1, 0).Selec
Loo

End Su

 

[EXLNeophyte]

Dear Gestas,

Thank you for the response. I pasted the VBC and ran it...it did searc
but none of the words was replaced. I check to make sure the colum
headings were correct...A, B, and D...check the cell contents to ensur
they were exact matches.

I'm trying to learn the code and syntax so please bear with me....

in the VBC you provided....

"sReplace = ActiveCell.Offset(0, 1).Value"

is this were if a match if found on sheet 1 column A that it shifts ove
one cell and replaces the "Find" with the contents of column B?

P.S. to speed it up I did relace ("d.d") with ("d1: d115")...makes sens
not to search the entire sheet. I assume I could do the same with

Range("A1").Select

EXLNeophyt

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Gestas]

Hi EXLNeophyte

I've got it working on a spreadsheet I've put together so maybe I'
misunderstanding what you're trying to do.

What you're saying re. sReplace is correct: if the value in column a i
found the value in column b is used to replace it.

For Range("a1").Select the loop statement will automatically move on t
cell a2 and so on so no need to change it in the code.

Here's a spreadsheet with the code running - Also with comments adde
that should explain what's going on when it's run.

Feel free to upload a sample spreadsheet if you have one and I'll take
look

+-------------------------------------------------------------------
|Filename: FindReplace.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=382
+-------------------------------------------------------------------

 

[EXLNeophyte]

Tanks for the update.

Your spreadsheet works great...I played around with it and I discovere
the reason why it did/does not work in my spreadsheet.

If the contents of cell "d" is just one word it will replace cat wit
dog and red with blue, etc. Here is what is happening...in my cell "d
I have a sentence "The man walked the dog." I thought just the wor
"dog" would be replaced with "cat"...instead it did nothing at all...
tested this by taking out the sentence and just putting in the wor
"dog", and it worked just the way it was supposed to...Funny, I woul
think that when I ran it the first time it would have replaced the whol
sentance (contents of the cell d sheet 2) with the one word replacemen
from column b sheet 1.

Your additional comments are great and I understand what is goin
on...well maybe not completely...so why would not the entire contents o
each cell in d be replace with the corresponding word from sheet
column d? and why does it not work when the word is embedded in
sentence?

I like the loop, simple and elegant...it sure beats having to do
counter for each cell, like I originally was doing.

This is great!..you have me thinking and I'm learning...any additiona
help with the above questions is greatly appreciated.

Thanks...EXLNeophyt

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Gestas]

What's happening is the code is looking for an identical match betwee
the cell in column A and the cell in column D

If you replace the For Each rCell section with the below which replace
the If-Then function with the Replace function

'''look in each cell in rang
For Each rCell In sht.Range("d1:d20").Cell
'''if the cell's value is equal to sFind then change its value t
sReplac
rCell.Value = Replace(rCell.Value, sFind, sReplace
'''continue onto next cell and repeat abov
Next rCel

it will search the column D text and replace 'Dog' wherever finds it
that should solve the issue you've found

One caveat is that it will literally replace the text wherever it find
it so it will change dog to cat but it will also for example chang
dogma to catma. If that's a problem you could write something to chec
if there are spaces before and after the word before replacing it. Th
in string (InStr) function could probably be used for that

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[EXLNeophyte]

Gestas,

Thank you so much for the tutorial, believe it or not I appreciat
learning something more than just having it done for me. I will pla
with this some more...I'm thinking I could color code the text I wan
replaced and have the Find and Replace just look for those strings i
red or maybe put the words in <dog> format.

Again, you were very helpful and I appreciate your time an
consideration.

EXLNeophyt

+-------------------------------------------------------------------
+-------------------------------------------------------------------