Find and Replace
 

Greetings,

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

I have a multi-page workbook. Sheet 1 column A contains a list of names, positions, addresses, etc. I would like to search Sheet 2, Sheet 3… column D for each occurrence in Sheet 1 column A. If a match is found then replace it with the contents of Sheet 1 column B same row 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 macro continues until all words on Sheet 1 Column A have been searched...there are more than 750 user entries containing letters and numbers.

Any help would be greatly appreciated.

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

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

Sub FindReplace()

Dim rCell As Range
Dim sFind As String
Dim sReplace As String
Sheets("Sheet1").Select
Range("A1").Select

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

For Each sht In Worksheets
If sht.Name < "Sheet1" Then

For Each rCell In sht.Range("d:d").Cells
If rCell.Value = sFind Then
rCell.Value = sReplace
End If
Next rCell

End If

Next sht
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Dear Gestas,

Thank you for the response. I pasted the VBC and ran it...it did search but none of the words was replaced. I check to make sure the column headings were correct...A, B, and D...check the cell contents to ensure 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 over 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 sense not to search the entire sheet. I assume I could do the same with

Range("A1").Select

EXLNeophyte

Hi EXLNeophyte

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

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

For Range("a1").Select the loop statement will automatically move on to 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 added 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 a look.

Tanks for the update.

Your spreadsheet works great...I played around with it and I discovered 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 with 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 word "dog" would be replaced with "cat"...instead it did nothing at all...I tested this by taking out the sentence and just putting in the word "dog", and it worked just the way it was supposed to...Funny, I would think that when I ran it the first time it would have replaced the whole sentance (contents of the cell d sheet 2) with the one word replacement from column b sheet 1.

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

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

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

Thanks...EXLNeophyte

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

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

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

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 finds it so it will change dog to cat but it will also for example change dogma to catma. If that's a problem you could write something to check if there are spaces before and after the word before replacing it. The in string (InStr) function could probably be used for that.

Gestas,

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

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

EXLNeophyte