Correct data with VBA

**HuskerBronco** · 09-29-2007, 10:19 AM

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** · 09-29-2007, 10:44 AM

Try this:

Dim acell As Range, f As Range

For Each acell In Worksheets("Sheet2").Range("A1:H72")
    With Worksheets("Sheet1").Range("A:A")
        Set f = .Find(acell)
        If Not f Is Nothing Then
            acell = f.Offset(0, 1)
        End If
    End With
Next

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

**HuskerBronco** · 09-29-2007, 03:52 PM

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** · 09-30-2007, 09:29 AM

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** · 09-30-2007, 09:15 PM

Hi

How about

Sub NameCorrection()

  Dim acell As Range, f As Range
  Dim FindRng As Range
  Set FindRng = Sheets("Validation").Range("A:A")

  With Sheets("Results of Sub Routine")
    For Each acell In .Range("E2:E" & .Cells(Rows.Count, "E").End(xlUp).Row)
      Set f = FindRng.Find(what:=acell.Value, lookat:=xlWhole)
      If Not f Is Nothing Then
        acell.Value = f.Offset(0, 1).Value
      End If
    Next acell
  End With


End Sub

rylo

**HuskerBronco** · 10-01-2007, 09:35 AM

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?:

    For Each acell In .Range("E2:E" & .Cells(Rows.Count, "E").End(xlUp).Row)
      Set f = FindRng.Find(what:=acell.Value, lookat:=xlWhole)

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.