Userform to find item price and description based on text box value

[RJSIGKITS]

Hi Guys - I think this one will be a really simple one for you that know, but I'm stuck!

So, I have a user form in which the user enters a product code into a textbox (TextBox1)
They hit the CommandButton to get a description and a price for that item.

I currently have this:

Private Sub CommandButton1_Click()Description = Application.WorksheetFunction.VLookup(TextBox1.Text, AllProds, 2, 0)
Price = Application.WorksheetFunction.VLookup(TextBox1.Text, AllProds, 8, 0)
End Sub

"Description" and "Price" are named Label fields, which should show the relevant results.
"Allprods" is the sheet name that has all the products on, which has approximately 30,000 products, and this data has also been saved as a TableArray, also called "AllProds".
Column 1 is the column that has the product codes in.
Column 2 is the column that has the Descriptions in.
Column 8 is the column that has the prices in.

This also needs to display a message if there are no items found for the code entered.

 

[Momentman]

Does this work for you?

Private Sub CommandButton1_Click()
    On Error GoTo errhandler:
    Description = Application.WorksheetFunction.VLookup(TextBox1.Text, Range("AllProds"), 2, 0)
    Price = Application.WorksheetFunction.VLookup(TextBox1.Text, Range("AllProds"), 8, 0)
errhandler:
    MsgBox "No items found matching this description"
End Sub

I assumed AllProds is a range name in the worksheet, if yes, then the above should work, if NO, you don't need the "Range" preceding it

 

[steve the fish]

This any good?

On Error resume next
Description = Application.WorksheetFunction.VLookup(TextBox1.Text, AllProds, 2, 0)
Price = Application.WorksheetFunction.VLookup(TextBox1.Text, AllProds, 8, 0)
On Error Goto 0

If Not IsError(Description) then
DescriptionLabel.Caption=Description
Else
DescriptionLabel.Caption="Error"
End if

If Not IsError(Price) then
PriceLabel.Caption=Price
Else
PriceLabel.Caption="Error"
End if

 

[RJSIGKITS]

Does this work for you?

Private Sub CommandButton1_Click()
    On Error GoTo errhandler:
    Description = Application.WorksheetFunction.VLookup(TextBox1.Text, Range("AllProds"), 2, 0)
    Price = Application.WorksheetFunction.VLookup(TextBox1.Text, Range("AllProds"), 8, 0)
errhandler:
    MsgBox "No items found matching this description"
End Sub

I assumed AllProds is a range name in the worksheet, if yes, then the above should work, if NO, you don't need the "Range" preceding it

Hey, Thank you, that's nearly there.
Currently, it throws up the error message even if it does find the results, though.
Also, is there a way to make the "Price" Label round up its value - currently returns a price of 69.999999997 for example, and I would need this to be 70.00

Thanks for your help.