Yes I see that now from your discussion with Bernie. At least you have
another tool in the armoury now <G

Bob

"Ed" wrote in message
...
Thanks for the reply, Bob.

although you only have a single test so I do not know why there is an OR
in
there
Ask John!! <g Maybe because arr1 is really not only 5 columns, but 10
rows by 5 columns, and I goofed in giving that info?

Actually, what I had more in mind was a way to use the array formula and
it's result entirely within VBA without having to write anything into a
cell.

Ed

"Bob Phillips" wrote in message
...
Dim x, arr1

x = "TheName"
arr1 = "NameList"
ActiveCell.FormulaArray = "=IF(OR(" & x & "=" & arr1 &
"),""Found"",""Not Found"")"


although you only have a single test so I do not know why there is an OR
in
there

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"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