Problem in VLOOKUP Function

Andrew_A

New Member
Joined
Apr 3, 2013
Messages
4
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.
PartNamePrice
1$23.00
2$45.00
3$67.00

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

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

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,565
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
It looks like it should work.

What message did you receive?

How did you Dim Partnum?
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
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:

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
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:

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514

ADVERTISEMENT

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
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,714
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
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Thanks Robert, thats gooder than gold.!!

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

Regards,
Howard
 

Jim885

Well-known Member
Joined
Jul 8, 2012
Messages
663
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,151
Members
414,214
Latest member
marketingnumbersguy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top