Using vLookup function in VBA

[Dale Fye]

I'm an Access programmer, venturing into Excel VBA.

I have a form that contains a text value ("1.1.6") that is one of many
values in my TaskInfo Worksheet. I want to use Vlookup to get the values in
the 2nd, 3rd, and 4th columns of this worksheet, but cannot seem to make it
work. My code segment looks like:

Dim lookupRange as range
set lookupRange = Worksheets("TaskInfo").Range("A2:A200")

me.text2.Value = application.worksheetfunction.vlookup("1.1.6", lookupRange,
2)

But this generates a runtime error 1004:
Unable to get the Vlookup property of the WorksheetFunction class

When set the lookupRange in the Immediate window, I am able to debug.print
lookupRange(1), etc, so I know the range is defined properly, but have know
idea why I am getting this error message.

Any help would be greatly appreciated.

 

[Jim Thomlinson]

You are looking for the value in the second column of a range that is only 1
column wide.

Dim lookupRange as range
set lookupRange = Worksheets("TaskInfo").Range("A2:B200") 'B200 not A

me.text2.Value = application.worksheetfunction.vlookup("1.1.6", lookupRange,
2)

 

[Dale Fye]

The VLookup Help indicates that the 1st parameter is the value to look for,
the 2nd is the range to look in (for that value), and the third is the column
from which to take the answer when the row is selected.

I don't want to look in column B for "1.1.6", I only want to look in column
A, but I want to return the value that is in column B.

Still did not get this working, but do have worksheetfunction.Match working,
which is better because I actually want to capture 3 or 4 columns worth of
data from that worksheet row, and this eliminates the need to lookup the
value multiple times.

Dale

 

[Jim Thomlinson]

If match works for you then great.

FYI help for Vlookup says "Searches for a value in the leftmost column of a
table". The second parameter is the table and it needs to include all of the
columns from which you might want to return data. Note the examples in help
all include multiple columns...

 

[Dave Peterson]

If you want to return the 2nd, 3rd, and 4th column of the lookup range, then
that lookup range has to have at least 4 columns (and the key in the leftmost
column).

Dim res as variant 'could be an error
dim myStr as string
dim LookupRng as range

mystr = "1.1.6"

set lookuprng = worksheets("Taskinfo").range("a2200") '<-- A to D!!!

'not application.worksheetfunction.vlookup!
res = application.vlookup(mystr, lookuprng, 2, false)
if iserror(res) then
me.text2.value = "No Match"
me.text3.value = "no match"
me.text4.value = "no Match"
else
me.text2.value = res 'use what you found
me.text3.value = application.vlookup(mystr, lookuprng, 3, false)
me.text4.value = application.vlookup(mystr, lookuprng, 4, false)
end if

I used false as that 4th argument. I would expect that since you're looking for
a match with strings, that you'd want an exact match.

============
Using application.match() (not application.worksheetfunction.match()):

Dim Res as variant
dim myStr as string
dim LookupRng as range

mystr = "1.1.6"

set lookuprng = worksheets("Taskinfo").range("a2:A200") '<-- Just Column A!!
res = application.match(mystr, lookuprng, 0)
if iserror(res) then
me.text2.value = "No Match"
me.text3.value = "no match"
me.text4.value = "no Match"
else
Me.Text2.Value = LookupRng(Res).Offset(0, 1).value
Me.Text3.Value = LookupRng(Res).Offset(0, 2).value
Me.Text4.Value = LookupRng(Res).Offset(0, 3).value
end if

I could have used this syntax, too:

Me.Text2.Value = LookupRng(Res, 2).Value
Me.Text3.Value = LookupRng(Res, 3).Value
Me.Text4.Value = LookupRng(Res, 4).Value

=============
The reason I used application.match instead of
application.worksheetfunction.match() is the way they handle errors (same reason
for the vlookup() version, too).

Application.match() returns a value that can be tested with
if iserror(res)

application.worksheetfunction.match() causes a run time error:

On error resume next
res = application.worksheetfunction.match(....)
if err.number <> 0 then
'no match
err.clear
me.text2.value = "no match"....
else
'worked ok
'do that
end if
on error goto 0

I find the application.match() and application.vlookup() easier to use.