# Problem in VLOOKUP Function

#### Andrew_A

##### New Member
Hi there,
Can someone tell me why this code does not run. I'm new to excel and just playing around with some basic functions

Sub GetPrice()
Dim PartName As Variant
Dim Price As Double
PartNum = InputBox("Enter the Part Number")
Price = WorksheetFunction.VLookup(PartNum, Range("B2:C5"), 2, False)
MsgBox PartNum & "Cost" & Price
End Sub

The table is very basic and I've added it below.
 PartName Price 1 \$23.00 2 \$45.00 3 \$67.00

<TBODY>
</TBODY><COLGROUP><COL span=2></COLGROUP>
 Thanks!

<TBODY>
</TBODY><COLGROUP><COL span=2></COLGROUP>

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
It looks like it should work.

How did you Dim Partnum?

Hi Andrew,

Try this:

Code:
``````Option Explicit
Sub GetPrice()

'Written by Trebor76
'Visit my website www.excelguru.net.au

Dim varPartNum As Variant

varPartNum = InputBox("Enter the Part Number")

If varPartNum = "" Then Exit Sub 'User clicked the Cancel button

If IsError(Evaluate("VLOOKUP(" & varPartNum & ",B2:C5,2,FALSE)")) = True Then
MsgBox "There is no entry for part number " & varPartNum
Else
MsgBox "The cost for part number " & varPartNum & " is " & Format(Evaluate("VLOOKUP(" & varPartNum & ",B2:C5,2,FALSE)"), "\$#,##0.00")
End If

End Sub``````

HTH

Robert

Last edited:
Hi Trebor76,

Understanding that this is "practice" code venture for Andrew_A, I was wondering how your code could accomodate a more realistic part number. One that is not just an integer of long, something like NSA123-44 and the like.

I tried Dim varPartNum As String but that did not play. So I'm guessing its in the Evaluate area of the code...?

Howard

Hi there,
Can someone tell me why this code does not run. I'm new to excel and just playing around with some basic functions

Sub GetPrice()
Dim PartName As Variant
Dim Price As Double
PartNum = InputBox("Enter the Part Number")
Price = WorksheetFunction.VLookup(PartNum, Range("B2:C5"), 2, False)
MsgBox PartNum & "Cost" & Price
End Sub
The reason that your code is failing is that the PartNum being extracted from the InputBox is a String, whereas the values in B2:B4 of your sheet are Numeric.

The problem is the same as shown in this screen shot.

Excel Workbook
BCDEFGH
21232#N/AFails because F2 is Text
3245245Works because F3 is numeric
4367
5
Demo

There are a couple of ways to fix your code.

1. Least preferred (by me) is to change the 'Price' line to
Rich (BB code):
``Price = WorksheetFunction.VLookup(CDbl(PartNum), Range("B2:C5"), 2, False)``

2. Preferred. Declare PartName as Double and stick with the 'Price' line that you had originally.
Rich (BB code):
``Dim PartNum As Double``

I think all variables should be declared. You can force yourself to do that by (in the vba window)
Tools|Options...|Editor tab|Check 'Require Variable Declaration'|OK

Note that with my suggested changes, the code will still fail if a value is entered in the InputBox that does not occur in the table. I was just addressing your question as to why your code was not working.

Last edited:
Hi Howard,

If you were to use my code for non numeric as well as numeric part numbers, the code has to be changed to add quotes for the former but not for the later (as Peter discusses nicely above), i.e.

Code:
``````Option Explicit
Sub GetPrice()

'Written by Trebor76
'Visit my website www.excelguru.net.au

Dim varPartNum As Variant

varPartNum = InputBox("Enter the Part Number")

If varPartNum = "" Then Exit Sub 'User clicked the Cancel button

If IsNumeric(varPartNum) Then
If IsError(Evaluate("VLOOKUP(" & varPartNum & ",B2:C5,2,FALSE)")) = True Then
MsgBox "There is no entry for part number " & varPartNum
Else
MsgBox "The cost for part number " & varPartNum & " is " & Format(Evaluate("VLOOKUP(" & varPartNum & ",B2:C5,2,FALSE)"), "\$#,##0.00")
End If
Else
If IsError(Evaluate("VLOOKUP(""" & varPartNum & """,B2:C5,2,FALSE)")) = True Then
MsgBox "There is no entry for part number " & varPartNum
Else
MsgBox "The cost for part number " & varPartNum & " is " & Format(Evaluate("VLOOKUP(""" & varPartNum & """,B2:C5,2,FALSE)"), "\$#,##0.00")
End If
End If

End Sub``````

HTH

Robert

Last edited:
Hi Robert,

Without the few modification I have made to your code it works well.

The mods I have made post the part number, the quanity of that part number and the price to the worksheet. On the sheet a few formulas keep a running total of all this info and a total cost of the items selected.

All my mods work fine except one.
The line in red will post the price of part numbers that are TEXT but will not post part numbers prices that are Numeric.

I don't know what to change to make that line post both.

Thanks.
Howard

Rich (BB code):
``````Option Explicit
Sub GetPriceNum_Text()
'Written by Trebor76
'Visit my website www.excelguru.net.au

'Dim AMT As Long
Dim varPartNum As Variant
Dim varPartQty As Long

varPartNum = InputBox("Enter the Part Number")
varPartQty = InputBox("How many of this Part Number")
Range("I100").End(xlUp).Offset(1, 0) = varPartNum
Range("J100").End(xlUp).Offset(1, 0) = varPartQty

If varPartNum = "" Then Exit Sub 'User clicked the Cancel button

If IsNumeric(varPartNum) Then
If IsError(Evaluate("VLOOKUP(" & varPartNum & ",B2:C10,2,FALSE)")) = True Then
MsgBox "There is no entry for part number " & varPartNum
Else
MsgBox "The cost for part number " & varPartNum & " is " _
& Format(Evaluate("VLOOKUP(" & varPartNum & ",B2:C10,2,FALSE)"), "\$#,##0.00")
End If
Else
If IsError(Evaluate("VLOOKUP(""" & varPartNum & """,B2:C10,2,FALSE)")) = True Then
MsgBox "There is no entry for part number " & varPartNum
Else
MsgBox "The cost for part number " & varPartNum & " is " _
& Format(Evaluate("VLOOKUP(""" & varPartNum & """,B2:C10,2,FALSE)"), "\$#,##0.00")

Range("K100").End(xlUp).Offset(1, 0) = Format(Evaluate("VLOOKUP(""" & varPartNum _
& """,B2:C10,2,FALSE)"), "\$#,##0.00")

End If
End If

End Sub``````

Hi Howard,

The logic is the same i.e. a line is needed if the entry is numeric like so:

Code:
``````Option Explicit
Sub GetPriceNum_Text()
'Written by Trebor76
'Visit my website www.excelguru.net.au

'Dim AMT As Long
Dim varPartNum As Variant
Dim varPartQty As Long

varPartNum = InputBox("Enter the Part Number")
varPartQty = InputBox("How many of this Part Number")
Range("I100").End(xlUp).Offset(1, 0) = varPartNum
Range("J100").End(xlUp).Offset(1, 0) = varPartQty

If varPartNum = "" Then Exit Sub 'User clicked the Cancel button

If IsNumeric(varPartNum) Then
If IsError(Evaluate("VLOOKUP(" & varPartNum & ",B2:C10,2,FALSE)")) = True Then
MsgBox "There is no entry for part number " & varPartNum
Else
MsgBox "The cost for part number " & varPartNum & " is " _
& Format(Evaluate("VLOOKUP(" & varPartNum & ",B2:C10,2,FALSE)"), "\$#,##0.00")

Range("K100").End(xlUp).Offset(1, 0) = Format(Evaluate("VLOOKUP(" & varPartNum _
& ",B2:C10,2,FALSE)"), "\$#,##0.00")
End If
Else
If IsError(Evaluate("VLOOKUP(""" & varPartNum & """,B2:C10,2,FALSE)")) = True Then
MsgBox "There is no entry for part number " & varPartNum
Else
MsgBox "The cost for part number " & varPartNum & " is " _
& Format(Evaluate("VLOOKUP(""" & varPartNum & """,B2:C10,2,FALSE)"), "\$#,##0.00")

Range("K100").End(xlUp).Offset(1, 0) = Format(Evaluate("VLOOKUP(""" & varPartNum _
& """,B2:C10,2,FALSE)"), "\$#,##0.00")
End If
End If

End Sub``````

HTH

Robert

Thanks Robert, thats gooder than gold.!!

I appreciate you taking the time beyond the original poster to help this lurker.

Regards,
Howard

Robert,
That code is great.
I have a question. What would be added to your VB code in order to bring a description of the item, which would be in Column C, to Column N?

Replies
3
Views
2K
Replies
3
Views
528
Replies
15
Views
3K
Replies
14
Views
975
Legacy 143009
L
Replies
7
Views
706

1,212,140
Messages
6,106,188
Members
448,004
Latest member
Umberto

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back