yee388
Well-known Member
- Joined
- Mar 7, 2004
- Messages
- 1,374
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!
*********
***************
I appreciate any and all input. Thanks!
Please help me with a For/Next loop or something to keep my code from getting too bulky!
*********
Code:
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!