VLookup Function in VB

D

DG

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
 
V

Vergel Adriano

DG,

Try it like this:

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

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)
 
B

Bob Phillips

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)
 
D

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
 
D

DG

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

Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLookup in VBA Help Needed 9
VLookUp Function 3
Vlookup in VBA 4
VLookup error 4
Using vLookup function in VBA 4
Vlookup VBA on dates 2
Vlookup property missing 3
Vlookup error 2

Top