Using Vlookup within VBA

[BlueMonkey]

Hi all

I had thought that using vlookup within a module would be fairly easy. I've got the code 'a = WorksheetFunction.VLookup("a", data, 2, False)', but I get an error message of "Unable to get the Vlookup property of the WorksheetFunction class.

Am I missing something?

Cheers

B.M.

 

[SkipVought]

Hi,

[tt]
a = Application.VLookup("a", data, 2, False)
[/tt]
try Application.

FYI data must be a named range.

Skip,
[sub]
[red][/red]
[/sub]

 

[BlueMonkey]

Thanks Skip. Data is already a range.

I tried with Application. and it generated a type mismatch. The holding variable contains "Error 2042".

We're using Excel 97 if that helps

B.M.

 

[SkipVought]

How do you have variable a defined? Should be a Variant.

Skip,
[sub]
[red][/red]
[/sub]

 

[Zathras]

The most likely problem is that "a" is not in the lookup range. It's the equivalent of getting #N/A when using VLOOKUP directly in the worksheet.

You need to trap for the error. (Or use some other method to guarantee that "a" will be in the lookup range.)

This works fine as long as "a" is in the lookup range:

Sub test()
Dim a As String
Dim data As Range
 Set data = Range("A1:B5")
 a = WorksheetFunction.VLookup("a", data, 2, False)
 MsgBox a
End Sub

 

[BlueMonkey]

Zathras, the formula itself is solid - I've copied the formula into the worksheet and it works. The formula shown above is only looking up a value from a 3 row table for testing purposes.

Skip, I'll check the variant and get back

B.M.