I need a loop!

[yee388]

I have the following VBA code to check for mistyped metal codes in individual cells between G7 and G27 in one sheet. They need to match exactly any other cell in the A column of another worksheet in another file. The only way I've gotten it two work is with a block of VBA for each cell. The code is stored in the workbook that the user types in G7:G27 as a SheetChange Event.

Please help me with a For/Next loop or something to keep my code from getting too bulky!

*********

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)

With Workbooks("Order Data").Worksheets("Precious Metals").Range("A:A")
        Set a = .Find(Range("G7").Value, LookIn:=xlValues)
        If a Is Nothing Then
            MsgBox ("No such code found. Please re-enter")
            Range("G7").Select
        End If
        
        Set b = .Find(Range("G8").Value, LookIn:=xlValues)
        If b Is Nothing Then
            MsgBox ("No such code found. Please re-enter")
            Range("G8").Select
        End If
        
        'and so on for G7 through G27....
        
End With

End Sub

***************

I appreciate any and all input. Thanks!

 

[yesterday]

I realize you've already put some effort into your VBA, but why don't you use the Validation tool under your Data menu?

 

[Cbrine]

Have you thought about using a vlookup to verify the codes with the orginal table?

=if(iserror(Vlookup(A1,'Sheet1!$A$1:$A$40,1,false)),"Wrong","")

A1 would be equal to the address of the value you are checking.
$A$1:$A$40 would be equal to the range of the table with the correct values. This could also be a named range.
Once you have the first one setup up, just copy or fill down for the rest of the values.

Hope this helps.

 

[DRJ]

Validation would probably be better, but here is your vba solution.

    With Workbooks("Order Data").Worksheets("Precious Metals").Range("A:A")
        For x = 7 To 27
            Set a = .Find(Range("G" & x).Value, LookIn:=xlValues)
            If a Is Nothing Then
                MsgBox ("No such code found. Please re-enter the value in G" & x)
                Range("G" & x).Select
                Exit Sub
                Else
            End If
        Next x
    End With

 

[yee388]

Validation would probably be better, but here is your vba solution.

I feel pretty dumb now for missing the Validation solution, but at least I learned that the Range method can take a variable.

And knowing is half the battle.

Thanks for the quick responses!