It's working, thanks. I think my problem was that the right worksheet
wasn't active. When I was playing with it originally, I was using a
named range instead of A1:E5, so that it didn't matter what sheet was
active; so I wasn't careful when I shifted over to experimenting with
Tom's code, which of course had in it the A1:E5 that I provided, and
that refers to the active sheet.

So now I'll go see if the same syntax will work with the COUNTIF situation.

Thanks for the help,
Alan Beban

Tom Ogilvy wrote:
I got this with 10035 in A3

1 B3
2 C3
3 D3
4 E3


As Dave said, if there is no match, just as in a worksheet, you get an error
if there is no match.

? CVErr(xlErrNA)
Error 2042


Regards,
Tom Ogilvy



Alan Beban wrote in message
...

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