Excel VBA code help

  VNAM75 01:30 15 Sep 2010
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 2010

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 2010

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 2010

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 2010

Thank you both for the solution.

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

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…

Best iPad buying guide 2017

Comment télécharger une application indisponible en France ?