Excel VBA code help

  VNAM75 01:30 15 Sep 10
Locked

The following code prompts the user for a number(PartNum)and uses that to lookup up and return a coresponding value in a table. But if the number entered does not have a corresponding value I get an error. How can the code be edited to bypass the error to say "value not in table,please re-enter another"?

Sub GetPrice()
Dim PartNum As Variant
Dim Price As Double
PartNum = InputBox(“Enter the Part Number”)
Sheets(“Prices”).Activate
Price = WorksheetFunction. _
VLookup(PartNum, Range(“PriceList”), 2, False)
MsgBox PartNum & “ costs “ & Price
End Sub

  KremmenUK 07:02 15 Sep 10

What is the error your getting ?

Not 100% sure about VBA, but in VB you can start the section with "on error goto errorhandler"

Sub GetPrice()
on error goto errorhandler
Dim PartNum As Variant
Dim Price As Double
PartNum = InputBox(“Enter the Part Number”)
Sheets(“Prices”).Activate
Price = WorksheetFunction. _
VLookup(PartNum, Range(“PriceList”), 2, False)
MsgBox PartNum & “ costs “ & Price
exit sub
:errorhandler
msgbox "Data not found",vbcritical,"Data Error"
End Sub

or something along those lines

  VNAM75 13:50 15 Sep 10

I haven't actually tried the code yet but its an example from a book I've got and states that it will "fail miserably" if you input a number that is not within the table. It refers me to the error handling chapter but I'm not sure which section to refer to or what code I need to add in. So if a valid number is put in it gives you the result, if not it prompts you to re-enter (like with a password).

  VoG II 15:32 15 Sep 10

Sub GetPrice()
Dim PartNum As Variant
Dim Price As Double
PartNum = Application.InputBox("Enter the Part Number", Type:=1)
Sheets("Prices").Activate
On Error Resume Next
Price = WorksheetFunction.VLookup(PartNum, Range("PriceList"), 2, False)
If Err = 0 Then
MsgBox PartNum & " costs " & Price
Else
MsgBox "No match", vbInformation
End If
End Sub

  VNAM75 15:41 15 Sep 10

Thank you both for the solution.

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

How Nearly Normal created super-fun papercraft for Google

47 iPhone camera tips to help you take better photos