Correct data with VBA

[HuskerBronco]

What type of construct do I use to achieve the following? :

Sheet1(A:A) contains an expandable list of INVALID NAMES
Sheet1(B:B) contains a list of CORRECTED NAMES

I want to write VBA code that will SELECT Sheet2 Range(A1:H72),
and check data in each cell for entries found in Sheet1(A:A),
and replace each occurrence with data in Sheet1(B:B).

Do I use a FOR EACH-NEXT, IF-THEN, SELECT CASE...or some other construct?

And, how would such a Sub Routine be written...???

[downforce]

Try this:

Please Login or Register  to view this content.

Obviously you'll need to customise it to your range but should be good.

[HuskerBronco]

downforce....

Thanks for your quick response to my question.

I was pretty close to the code that you've suggested, but I encountered a problem with MY code...as well as yours.

I pasted YOUR code into my Sub Routine, while deleting my own code.
I ran the Sub Routine...and everything appeared to run smoothly.

Closer inspection revealed inaccurate "positives" to the FIND command.

I've zipped a copy of a small section of my workbook that should clearly illustrate the flaw in the code. One Worksheet shows a sample list of the INCORRECT & CORRECTED NAMES. The second Worksheet shows the results after running the Macro. The errors are highlighted in yellow.

(Click the button to run the Sub Routine).

I think you will clearly see the problem that I've encountered.

[HuskerBronco]

Been scouring the internet trying to figure this one out.

I need the FIND function to find EXACT matches, only.

I'm beginning to think that this can't be done with basic VBA.
I think I am going to have to "call" some Excel functions with the Sub Routine.

So...it's back-to-the-books to learn how to do that...
Or return to the formula-based solution that I was using before.

[rylo]

Hi

How about

Please Login or Register  to view this content.

rylo

[HuskerBronco]

rylo....

As you have done many times before, you've solved my problem for me with brilliant code.

Not only did you resolve my immediate situation...(using VBA to FIND EXACT MATCHES)...
...you produced code that addresses a question that I had signed on to search and\or post.

That is: How do you write VBA to determine the last occupied cell in a column or range?
As far as I can tell...isn't that what you are doing with THIS part of your code?:

Please Login or Register  to view this content.

I'm thinking that if downforce had realized that I needed EXACT matches, he would've provided similar code.
I thank you both, for your excellent guidance.


BTW...
I usually do a Spell Check before submitting my posts.
I found the "Suggested Replacement" for xlWhole kinda humorous.
Spell Check suggested that I replace xlWhole...with hellhole.