Problem in VLOOKUP Function

A

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>
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
It looks like it should work.

What message did you receive?

How did you Dim Partnum?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
Andrew_A said:
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
Click to expand...
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thanks Robert, thats gooder than gold.!!

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

Regards,
Howard
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
341
Agnarr
Agnarr
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
2K
profray
P
G
  • Question
Run -time error '1004' Method 'Range' of object'_Global' failed
Replies
3
Views
561
GJennin7
G
A
  • Question
Web Scraping using vba
2
Replies
15
Views
3K
Abdmujib
A
T
  • Solved
Visual Basic: inserting rows, merging specific cells and adding specific text in excel
2
Replies
14
Views
1K
Legacy 143009
L

Forum statistics

Threads
1,214,827
Messages
6,121,817
Members
449,049
Latest member
cybersurfer5000

Share this page

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
Back
Top