You'll get the error 2042 if there wasn't a match for the 10035 in A1:A5.

Is it missing or text?



Alan Beban wrote:

Tom,

I'm using xl2000. When I ran the code step by step it went from
If IsArray(varr) Then to End If. When I ran it again with
Debug.Print varr immediately after the Evaluate line it printed Error
2042. Did you get something different? In what version?

Thanks,
Alan Beban

Tom Ogilvy wrote:
Generally, to do an array formula you need to use evaluate:

Sub TestVlook()
Dim varr As Variant
Dim i As Long
varr = Evaluate("VLookup(10035,A1:E5,{2, 3, 4, 5}, False)")
If IsArray(varr) Then
For i = LBound(varr) To UBound(varr)
Debug.Print i, varr(i)
Next
End If
End Sub

Regards,
Tom Ogilvy


Alan Beban wrote in message
...

Rob Bovey, Dave Peterson and Dana DeLouis recently provided helpful
suggestions for a similar situation with COUNTIF. None of them seems to
resolve the following superficially similar problem with VLOOKUP.

=VLOOKUP(10035,A1:E5,{2,3,4,5},FALSE)

works fine on the worksheet.

With the VLookups function from my website, either of the following VBA
code snippets works as well:

arr = VLookups(10035, Range("tbl2"),Application.Evaluate("{2,3,4,5}"))

arr = VLookups(10035, Range("tbl2"), Evaluate("{2,3,4,5}"))

But notwithstanding the above helpful suggestions for COUNTIF, I can't
get anything like the following to work:

arr = Application.VLookup(10035,A1:E5,????????,False)

Any more helpful suggestions?

Thanks in advance,
Alan Beban





--

Dave Peterson