I have a userform used to input a supply order using item number. The stock
items are listed on worksheet("Items"). I do not want to use a combo or
listbox. I want the user to enter the item number and then have a macro
check the range (A3:A, using xldown to determine actual range) and see if the
item number entered is a valid number before advancing to quantity field.

I thought about reading the data into an array and looping each time to
check but figure there is a quicker and easier way.

Thank,
Les

Hi,

try something like this
Sub Macro1()
Range("A3").Select
X = InputBox("Input", "Title")

Range(Selection, Selection.End(xlDown)).Select
Selection.Find(What:=(X), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End Sub

"WLMPilot" wrote:

I have a userform used to input a supply order using item number. The stock
items are listed on worksheet("Items"). I do not want to use a combo or
listbox. I want the user to enter the item number and then have a macro
check the range (A3:A, using xldown to determine actual range) and see if the
item number entered is a valid number before advancing to quantity field.

I thought about reading the data into an array and looping each time to
check but figure there is a quicker and easier way.

Thank,
Les

Hi Les, I agree with David that the Find method is probably the fastest.
You can set up a validation loop with a message box in case the data entered
by the user is not found, so that it will recycle to the input box for the
user to make correction to their original entry or to just go to the next
item. That will speed up the process, rather than have the procedure
terminate because there was no match.

"WLMPilot" wrote:

I have a userform used to input a supply order using item number. The stock
items are listed on worksheet("Items"). I do not want to use a combo or
listbox. I want the user to enter the item number and then have a macro
check the range (A3:A, using xldown to determine actual range) and see if the
item number entered is a valid number before advancing to quantity field.

I thought about reading the data into an array and looping each time to
check but figure there is a quicker and easier way.

Thank,
Les

That was my next question. How do I trap if value not found? I have a
couple of books I used to understand the FIND function. The user will input
a three digit number (as a string). I need to match cell content, which will
hold a three digit number formatted to text. I figure the WHAT, SEARCHORDER,
and SEARCHDIRECTION is all I need, but I do not know how to trap if value not
found.

Les

"JLGWhiz" wrote:

Hi Les, I agree with David that the Find method is probably the fastest.
You can set up a validation loop with a message box in case the data entered
by the user is not found, so that it will recycle to the input box for the
user to make correction to their original entry or to just go to the next
item. That will speed up the process, rather than have the procedure
terminate because there was no match.

"WLMPilot" wrote:

I have a userform used to input a supply order using item number. The stock
items are listed on worksheet("Items"). I do not want to use a combo or
listbox. I want the user to enter the item number and then have a macro
check the range (A3:A, using xldown to determine actual range) and see if the
item number entered is a valid number before advancing to quantity field.

I thought about reading the data into an array and looping each time to
check but figure there is a quicker and easier way.

Thank,
Les

This is a generic Find statement with a validation
loop built in. It assumes there would only be one
occurence of the ItemToFind, if any. You can
modify the find statement with more arguments and
also include an option to exit the Sub instead of
looping. You can also modify it to do a Find Next.

Sub TrapNoFind()
Dim c As Range, ItemToFind As Variant
Retry:
ItemToFind = InputBox("Enter Item to Find", "Title")
Set c = Worksheets(1).Range("A2:A50") _
.Find(ItemToFind, LookIn:=xlValues)
If Not c Is Nothing Then
'Do something
Else
GoTo Retry:
End If
End Sub

"WLMPilot" wrote:

That was my next question. How do I trap if value not found? I have a
couple of books I used to understand the FIND function. The user will input
a three digit number (as a string). I need to match cell content, which will
hold a three digit number formatted to text. I figure the WHAT, SEARCHORDER,
and SEARCHDIRECTION is all I need, but I do not know how to trap if value not
found.

Les

"JLGWhiz" wrote:

Hi Les, I agree with David that the Find method is probably the fastest.
You can set up a validation loop with a message box in case the data entered
by the user is not found, so that it will recycle to the input box for the
user to make correction to their original entry or to just go to the next
item. That will speed up the process, rather than have the procedure
terminate because there was no match.

"WLMPilot" wrote:

I have a userform used to input a supply order using item number. The stock
items are listed on worksheet("Items"). I do not want to use a combo or
listbox. I want the user to enter the item number and then have a macro
check the range (A3:A, using xldown to determine actual range) and see if the
item number entered is a valid number before advancing to quantity field.

I thought about reading the data into an array and looping each time to
check but figure there is a quicker and easier way.

Thank,
Les

Thanks for your help. Just what I needed.

Les

"David" wrote:

Hi,

try something like this
Sub Macro1()
Range("A3").Select
X = InputBox("Input", "Title")

Range(Selection, Selection.End(xlDown)).Select
Selection.Find(What:=(X), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End Sub

"WLMPilot" wrote:

I have a userform used to input a supply order using item number. The stock
items are listed on worksheet("Items"). I do not want to use a combo or
listbox. I want the user to enter the item number and then have a macro
check the range (A3:A, using xldown to determine actual range) and see if the
item number entered is a valid number before advancing to quantity field.

I thought about reading the data into an array and looping each time to
check but figure there is a quicker and easier way.

Thank,
Les

Thanks for your help. I googled the FIND function and after adapting to what
I needed and finding out how to trap with the IF statement, got it to work
for the most part. Still working out clitches.

I would like an explanation on the IF statement you indicate. Just reading
it makes no sense to me, ie IF Not c Is Nothing. The way I figured out what
this meant was reaching back into math class and saying two negatives (ie IF
NOT...IS NOTHING) make a positive, ie if entry is valid (item number found),
then do something ELSE reenter item number.

How exactly is this code deteremined?

Thanks,
Les



"JLGWhiz" wrote:

This is a generic Find statement with a validation
loop built in. It assumes there would only be one
occurence of the ItemToFind, if any. You can
modify the find statement with more arguments and
also include an option to exit the Sub instead of
looping. You can also modify it to do a Find Next.

Sub TrapNoFind()
Dim c As Range, ItemToFind As Variant
Retry:
ItemToFind = InputBox("Enter Item to Find", "Title")
Set c = Worksheets(1).Range("A2:A50") _
.Find(ItemToFind, LookIn:=xlValues)
If Not c Is Nothing Then
'Do something
Else
GoTo Retry:
End If
End Sub

"WLMPilot" wrote:

That was my next question. How do I trap if value not found? I have a
couple of books I used to understand the FIND function. The user will input
a three digit number (as a string). I need to match cell content, which will
hold a three digit number formatted to text. I figure the WHAT, SEARCHORDER,
and SEARCHDIRECTION is all I need, but I do not know how to trap if value not
found.

Les

"JLGWhiz" wrote:

Hi Les, I agree with David that the Find method is probably the fastest.
You can set up a validation loop with a message box in case the data entered
by the user is not found, so that it will recycle to the input box for the
user to make correction to their original entry or to just go to the next
item. That will speed up the process, rather than have the procedure
terminate because there was no match.

"WLMPilot" wrote:

I have a userform used to input a supply order using item number. The stock
items are listed on worksheet("Items"). I do not want to use a combo or
listbox. I want the user to enter the item number and then have a macro
check the range (A3:A, using xldown to determine actual range) and see if the
item number entered is a valid number before advancing to quantity field.

I thought about reading the data into an array and looping each time to
check but figure there is a quicker and easier way.

Thank,
Les