I need a loop!

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!

*********
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!
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

yesterday

New Member
Joined
Mar 5, 2003
Messages
34
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Validation would probably be better, but here is your vba solution.

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

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
DRJ said:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,574
Messages
5,596,942
Members
414,113
Latest member
mboo86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top