VLookup Function in VB
 

Why does this not work:

Sub TEST()
Dim TESTVAR As Integer
Sheets("DataQuery").Select
TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 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

VLookup Function in VB
 

DG,

Try it like this:

TESTVAR = WorksheetFunction.VLookup("COBRA", Range("A1:D20"), 4)


--
Hope that helps.

Vergel Adriano


"DG" wrote:

Why does this not work:

Sub TEST()
Dim TESTVAR As Integer
Sheets("DataQuery").Select
TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 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

VLookup Function in VB
 

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)


DG wrote:

Why does this not work:

Sub TEST()
Dim TESTVAR As Integer
Sheets("DataQuery").Select
TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 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

--

Dave Peterson

VLookup Function in VB
 

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


--
---
HTH

Bob

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



"DG" wrote in message
...
Why does this not work:

Sub TEST()
Dim TESTVAR As Integer
Sheets("DataQuery").Select
TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 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

VLookup Function in VB
 

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

"Vergel Adriano" wrote in message
...
DG,

Try it like this:

TESTVAR = WorksheetFunction.VLookup("COBRA", Range("A1:D20"), 4)


--
Hope that helps.

Vergel Adriano


"DG" wrote:

Why does this not work:

Sub TEST()
Dim TESTVAR As Integer
Sheets("DataQuery").Select
TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 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

VLookup Function in VB
 

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

Thanks.

"Dave Peterson" wrote in message
...
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)


DG wrote:

Why does this not work:

Sub TEST()
Dim TESTVAR As Integer
Sheets("DataQuery").Select
TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 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

--

Dave Peterson