Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 Quote:
Last edited by Gestas : May 17th 12 at 03:56 PM Reason: typo |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. Last edited by Gestas : May 19th 12 at 08:37 PM Reason: . |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
Find/Replace Event or Find/Replace for Protected Sheet ... | Excel Programming | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
Replace method - cannot find any data to replace | Excel Programming |