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

"Myrna Larson" wrote in message
...
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

On Mon, 04 Aug 2003 17:20:47 -0700, Alan Beban wrote:

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