Which wouldn't work if your 5 column array of names was also multirow
Oh, yes - sorry! In the book's example, the array was 10 rows by 5 cols.

You could write the array formula to a cell and
evaluate it there, capturing the value by reading the cell value, but it
is better, IMO, to use the
simplest method depending on where you are
I had thought about writing the formula to a cell. But it introduced another
element. If you're processing a large array, writing every value to check
into a cell, recalculating the formula, and reading the answer back into
code could become a real time factor. I knew there was a way to evaluate
basic worksheet formulas in VBA code; I was hoping the same thing could be
done for array formulas.

Ed

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Ed,

The logic is usually different when using VBA. You could write the array
formula to a cell and
evaluate it there, capturing the value by reading the cell value, but it
is better, IMO, to use the
simplest method depending on where you are, and that is the MATCH solution
that I gave. For a 1
dimensional array of names, I'm sure that I would've used the MATCH
solution on the worksheet as
well:

=IF(ISERROR(MATCH(TheName,NameList,False)),"Not Found", "Found")

Which wouldn't work if your 5 column array of names was also multirow.

HTH,
Bernie
MS Excel MVP


"Ed" wrote in message
...
What I get from your response, Bernie, is a very useful piece of code
(Thank
You!!) and the basic premise that there is no direct translation of an
array
formula into VBA code? Instead, you must dissect the array formula and
work
out the matching functions and such?

Ed

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Ed,

For example:

Dim x As String
Dim arr1 As Variant
Dim IsThere As Boolean

arr1 = Array("Test1", "Test2", "Test3")

x = "Test2"
IsThere = Not IsError(Application.Match(x, arr1, False))
MsgBox "Match of " & x & " is " & IsThere

x = "Test4"
IsThere = Not IsError(Application.Match(x, arr1, False))
MsgBox "Match of " & x & " is " & IsThere

HTH,
Bernie
MS Excel MVP


"Ed" wrote in message
...
I'm just getting my feet wet in using array formulas. I'm beginning
to
get
a glimpse of their usefulness. Unfortunately, I work almost
exclusively
from within VBA, vice entering a formula on the worksheet. I grabbed
(I
think!!) how to enter an array formula into the worksheet using VBA.
But
somehow it's slipping past me how to use an array formula entirely
within
VBA code.

For example, from John Walkenbach's book "Excel 2000 Formulas", he
gives
an
array formula for determining if a range contains a particular value.
In
his example, a single cell is named "TheName", and a 5-column array
of
names
is named "NameList". His array formula is {=IF(OR(TheName=NameList),
"Found", "Not Found")}.

If I want to do something like this entirely within VBA, then TheName
is
likely to be a variable "x" and NameList an array "arr1". And I do
not
want
to display text in a cell, but do this if true, do that if false. Am
I
barking up the wrong tree on this? Or is there just a simple thing
I'm
missing?

Ed