VLookup Function in VB

[DG]

Why does this not work:

Sub TEST()
Dim TESTVAR As Integer
Sheets("DataQuery").Select
TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A120", 4)
End Sub

I get a Run-time error '1004'
Unable to get the VLookup property of the WorksheetFunction class

Data looks something like this:

Supplier Item Qty Cost
AEARO AEA 123 5 2.50
COBRA COB 123 8 4.50
etc...

DG

 

[Guest]

DG,

Try it like this:

TESTVAR = WorksheetFunction.VLookup("COBRA", Range("A120"), 4)

 

[Dave Peterson]

Since you're matching on a string, wouldn't you want the match to be exact? I'm
gonna assume that you'd answer yes:

Option Explicit
Sub Test()
dim Res as variant
dim VlookupRng as range
dim WhatWord as string

set vlookuprng = worksheets("dataquery").range("a1:d20")

whatword = "Cobra"

res = application.vlookup(whatword, vlookuprng, 4, false)

if iserror(res) then
msgbox "No match!"
else
msgbox res
end if

End Sub

(Untested, uncompiled)

You could have used:

res = application.vlookup("cobra", worksheets("dataquery").range("a1:d20"), _
4, false)

 

[Bob Phillips]

Sub TEST()
Dim TESTVAR As Integer
Sheets("DataQuery").Select
TESTVAR = Application.WorksheetFunction.VLookup("COBRA",
Range("A120"), 4)
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[DG]

I tried that and got the same error message.

That's when I put the Sheets("DataQuery").Select statement in thinking that
it didn't have the focus on any sheet. But still getting the same error.

DG

 

[DG]

You are correct I did want it to be exact. I haven't tried the long version
but your second example worked.

Thanks.