Verifying input

[Guest]

I have a workbook that contains two sheets (Items, Order). The Order sheet
is what the user uses to input data to place an order, ie item number and
qty. The Items sheet list the item number and item description. I am using
a macro to control cursor movement (left -> right) for entering item number
and qty before advancing to the next row for the next item.

I am trying to figure out how to check the item number entered by user
against the item numbers in the list on the Items sheet (A2:A300), to make
sure that the item number entered is valid.

Below is the code used to control cursor movement. It is within this code
that I need to validate the item number and NOT advance until a valid number
is entered. I can figure out how to display a message box to notify user of
incorrect number. Just need the code. NOTE: Prior to entering this area of
code, Cell "A13" has already been selected via RANGE ("A13").Select.

If Not Intersect(Target, Range("A13:B90")) Is Nothing Then
If Target.Column = 1 Then
Target.Offset(, 1).Select 'advance to col B for qty
Else
Target.Offset(1, -1).Select 'advance to next row, col A for item
number
End If
End If

Also, when checking the list in the Items sheet, can A:A be used instead of
A2:A300 in the event new items are added in order to not have to go back and
edit the range to search in the macro?

Thanks,
Les

 

[Tom Ogilvy]

Dim rng as Range, res as Variant

If Not Intersect(Target, Range("A13:B90")) Is Nothing Then

If Target.Column = 1 Then
set rng = Worksheets("Items").Range("A:A")
res = application.Match(target,rng,0)
if iserror(res) then
msgbox "Bad entry, try again"
exit sub
end if
Target.Offset(, 1).Select 'advance to col B for qty
Else
Target.Offset(1, -1).Select 'advance to next row, col A for item
number
End If
End If