Why cant I pass a Variant as the lookup value to a VBA VLookup?

user_3105869 0
Viewed 497

I'm trying to learn a bit of VBA, and am hoping someone can explain something for me

I'm running a vlookup in VBA that takes a numeric index and returns the corresponding name from worksheet range. If I pass this index as a Variant the function fails, but if I pass an Integer it succeeds. However, if I change the underlying range data from numeric to alphanumeric (e.g., from 1 to 1a) the Variant argument works.

Why does does vlookup fail when a Variant argument is received and the underlying data is numeric?

I have a worksheet that looks like this:
Lookup range setup

This lookup function works perfectly

Sub LookupTest()
 Dim LookupValue As Integer
 Dim ReturnValue As Variant
 LookupValue = InputBox("Enter a value to lookup")
 Sheets("Sheet1").Activate
 ReturnValue = WorksheetFunction.VLookup(LookupValue, Range("A1:B4"), 2, False)
 MsgBox (ReturnValue)
End Sub

But this one fails:

Sub LookupTest()
    Dim LookupValue As Variant
    Dim ReturnValue As Variant
    LookupValue = InputBox("Enter a value to lookup")
    Sheets("Sheet1").Activate
    ReturnValue = WorksheetFunction.VLookup(LookupValue, Range("A1:B4"), 2, False)
    MsgBox (ReturnValue)
End Sub
vlookup vba excel
1 Answers

It's failing because your input box is stuffing text-that-looks-like-a-number into a variant and "9" does not equal 9. A variant can be either text or a number so if it is given text that looks like a number, that is what you get. An integer type var cannot be text-that-looks-like-a-number so VBA's overhead makes the conversion and your first example works.

Two options:

First, you can use application.inputbox which allows you to return a true number, not text.

LookupValue = application.InputBox("Enter a value to lookup", type:=1)

Second, you could take the text that your own inputbox returned and convert it to a true number in the vlookup.

ReturnValue = WorksheetFunction.VLookup(val(LookupValue), Range("A1:B4"), 2, False)
Admin1
Related