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("a2:D200") '<-- 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.





Dale Fye wrote:

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.

--
Email address is not valid.
Please reply to newsgroup only.

--

Dave Peterson