vba unable to get VLookup property

Question

I am new for vba programming. Could anyone help me out of this problem:

Sub GetPrice()
    Dim PartNum As Variant
    Dim Price As Variant
    PartNum = InputBox("Enter the Part Number")
    Sheets("Sheet2").Activate
    Price = WorksheetFunction._
    VLookup(PartNum, Range("A2:C20"), 2, False)
    MsgBox PartNum & "costs" & Price
End Sub

Every time I try to run this code, the input box is ok, but then follows the error:unable to get VLookup property... after I ty

Any help appreciated.

possible duplicate of [Writing a VLOOKUP function in vba](http://stackoverflow.com/questions/5567513/writing-a-vlookup-function-in-vba) — Isaac G Sivaa, Jul 07 '14 at 19:53
Definite duplicate of [How to error handle WorksheetFunction.VLookup?](http://stackoverflow.com/questions/18063214/how-to-error-handle-worksheetfunction-vlookup). You need error handling in this code to account for the possibility that the searched value does not exist. — David Zemens, Jul 07 '14 at 20:12
In particular Doug's answer will be of use to you: http://stackoverflow.com/a/18064104/1467082 — David Zemens, Jul 07 '14 at 20:15

score 0 · Answer 1 · answered Jul 07 '14 at 19:43

You'll get a somewhat ambiguous error if the look up value isn't in your look up table. Try wrapping it in some error correction to see if it helps:

Sub GetPrice()
    Dim PartNum As Variant
    Dim Price As Variant
    PartNum = InputBox("Enter the Part Number")
    Sheets("Sheet2").Activate
    On Error GoTo out
    Price = WorksheetFunction.VLookup(PartNum, Sheet2.Range("A2:C20"), 2, False)
    MsgBox PartNum & "costs" & Price
    Exit Sub

out:
MsgBox "Part not found"
End Sub

score 0 · Answer 2 · answered Jul 07 '14 at 19:48

Sub GetPrice()
    Dim PartNum As Variant
    Dim Price As Variant
    PartNum = InputBox("Enter the Part Number")
    Price = Application.VLookup(PartNum, Sheets("Sheet2").Range("A2:C20"), 2, False)
    If Not IsError(Price) Then
        MsgBox PartNum & " costs " & Price
    Else
        MsgBox "Part Number '" & PartNum & "' was not found!"
    End If
End Sub

Tephelon · Answer 3 · 2014-07-07T20:12:29.657

I personally always use Cells([rowIndex], [columnIndex]) in Excel macros. This is the most basic (and most of the time the only) function you need to interact with your data. VLookUp is good if you try to get only the value, not the postition. Using Cells() you have more power.

This is an implementation which searches for a specific string in a defined part of your sheet:

Sub searchPartNumber()
Dim ret() As Integer
'initialize the cell space to search in.
Dim fromCell(1) As Integer
Dim toCell(1) As Integer
'index 0: row
'index 1: column

'(A,2):(C,20) looks like this:
fromCell(0) = 1
fromCell(1) = 2
toCell(0) = 3
toCell(1) = 20

PartNum = InputBox("Enter the Part Number")
ret = searchTable(PartNum, fromCell, toCell)
If ret(0) = 1 Then
    MsgBox ("The value searched is in Cell " & ret(1) & ", " & ret(2) & " and the value searched for is " & Cells(ret(1), 2))
Else
    MsgBox "This part number could not be found."
End If
End Sub

Function searchTable(ByVal searchValue As String, ByRef fromCell() As Integer, ByRef toCell() As Integer) As Integer()
Dim ret(2) As Integer
'index 0: 1 = found, 0 = not found
'index 1/2: row and column of found element
ret(0) = 0
Dim i As Integer, j As Integer
For i = fromCell(0) To toCell(0)
    For j = fromCell(1) To toCell(1)
        If (CStr(Cells(i, j)) = searchValue) Then
            'Item found
            ret(0) = 1
            ret(1) = i
            ret(2) = j
            searchTable = ret
            Exit Function
        End If
    Next
Next
End Function

score 0 · Answer 4 · answered Jul 09 '14 at 14:23

0

Thank you all!

I think I find out what the problem is. I cannot define “PartNum” as Variant ( which I still didn't get? I think that Variant can replace any potential type of data ).

After I defined “PartNum” as Integer or Double, I can run this vlookup function very well because the value I try to look up is actually numbers!

answered Jul 09 '14 at 14:23

Jin

1

Well yes `PartNum = InputBox("Enter the Part Number")` will treat PartNum as a String because InputBox returns a String that represents the Text property of the TextBox in the InputBox form. VLOOKUP is quite particular about not looking up number fields with text and vice versa. You would have to either not define as variant (as you did) or cast like `CInt(InputBox(...))`. Does that make sense to you as to why Variant fails? – Mark Balhoff Jul 09 '14 at 18:12

vba unable to get VLookup property

4 Answers4