VBA Syntax for VLOOKUP to return array of return values

[Alan Beban]

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

 

[Tom Ogilvy]

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]

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

 

[Dave Peterson]

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

Is it missing or text?

 

[Myrna Larson]

Hi, Alan:

My experience with evaluating array formulas in VBA has been that it can't be trusted. One day
the code will work, the next day -- without any changes to the worksheet or the code -- it
doesn't.

So I've given up on that and write the required loops.

Myrna Larson

 

[Tom Ogilvy]

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]

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

 

[Charles Williams]

Hi Myrna,

One reason for your problems may be that using Application.Evaluate with
references unqualified by sheets always refers to the active sheet, so you
get a different answer depending on which sheet is active.
You can use Worksheet.Evaluate to control this behaviour.


hth
Charles Williams
www.DecisionModels.com