Brain lock: using array formula within VBA?

[Ed]

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

[Bernie Deitrick]

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" <ed_millis@NO_SPAM.yahoo.com> wrote in message news:ue%[email protected]...
> 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
>
>

[Bob Phillips]

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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:ue%[email protected]...
> 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
>
>

[Ed]

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
news:%[email protected]...
> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:ue%[email protected]...
> > 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
> >
> >
>
>

[Bernie Deitrick]

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" <ed_millis@NO_SPAM.yahoo.com> wrote in message news:[email protected]...
> 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
> news:%[email protected]...
>> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:ue%[email protected]...
>> > 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
>> >
>> >
>>
>>
>
>

[Ed]

> 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
news:%235Ao4%[email protected]...
> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> > 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
> > news:%[email protected]...
> >> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:ue%[email protected]...
> >> > 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Bernie Deitrick]

Ed,

Actually, if you have the array formula as a string (or as a string that you can build), you can use
the Evaluate method:

MsgBox Evaluate("=IF(OR(TheName=NameList), ""Found"", ""Not Found"")")

HTH,
Bernie
MS Excel MVP


"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message news:[email protected]...
> 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
> news:%[email protected]...
>> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:ue%[email protected]...
>> > 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
>> >
>> >
>>
>>
>
>

[Ed]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:ue%[email protected]...
> > 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
> >
> >
>
>

[Bernie Deitrick]

Ed,

Actually, if you have the array formula as a string (or as a string that you can build), you can use
the Evaluate method:

MsgBox Evaluate("=IF(OR(TheName=NameList), ""Found"", ""Not Found"")")

Still, I would use this for the multi-row multi-column list, since I hate having to build workable
formula strings:

MsgBox IIf(Range("NameList").Find(Range("TheName").Value, _
lookAt:=xlWhole) Is Nothing, "Not Found", "Found")

There's usually about a hundred different ways that you can do the same thing in VBA. It's often a
matter of style...

HTH,
Bernie
MS Excel MVP


"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message news:[email protected]...
>> 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
> news:%235Ao4%[email protected]...
>> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:[email protected]...
>> > 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
>> > news:%[email protected]...
>> >> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
>> > news:ue%[email protected]...
>> >> > 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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Bob Phillips]

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

Bob

"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:ue%[email protected]...
> > > 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
> > >
> > >
> >
> >
>
>

[Ed]

Ah, the missing bit! Tom Ogilvy had given me the Evaluate method in an
earlier post, but I didn't understand about putting the entire formula in a
string. This can open up a lot of room to play!

Once again, Bernie, I'm grateful for your help.
Ed

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%[email protected]...
> Ed,
>
> Actually, if you have the array formula as a string (or as a string that
you can build), you can use
> the Evaluate method:
>
> MsgBox Evaluate("=IF(OR(TheName=NameList), ""Found"", ""Not Found"")")
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> > 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
> > news:%[email protected]...
> >> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:ue%[email protected]...
> >> > 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Bob Phillips]

If you are going to use Evaluate a lot, best to qualify it with the
worksheet that the evaluation is being acted upon, avoids any potential
problems in addressing the activesheet.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> Ah, the missing bit! Tom Ogilvy had given me the Evaluate method in an
> earlier post, but I didn't understand about putting the entire formula in
a
> string. This can open up a lot of room to play!
>
> Once again, Bernie, I'm grateful for your help.
> Ed
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:%[email protected]...
> > Ed,
> >
> > Actually, if you have the array formula as a string (or as a string that
> you can build), you can use
> > the Evaluate method:
> >
> > MsgBox Evaluate("=IF(OR(TheName=NameList), ""Found"", ""Not Found"")")
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:[email protected]...
> > > 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
> > > news:%[email protected]...
> > >> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > > news:ue%[email protected]...
> > >> > 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
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
>

[Ed]

Thank you for that advice, Bob. I'll remember that.
Ed

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> If you are going to use Evaluate a lot, best to qualify it with the
> worksheet that the evaluation is being acted upon, avoids any potential
> problems in addressing the activesheet.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:[email protected]...
> > Ah, the missing bit! Tom Ogilvy had given me the Evaluate method in an
> > earlier post, but I didn't understand about putting the entire formula
in
> a
> > string. This can open up a lot of room to play!
> >
> > Once again, Bernie, I'm grateful for your help.
> > Ed
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:%[email protected]...
> > > Ed,
> > >
> > > Actually, if you have the array formula as a string (or as a string
that
> > you can build), you can use
> > > the Evaluate method:
> > >
> > > MsgBox Evaluate("=IF(OR(TheName=NameList), ""Found"", ""Not Found"")")
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > >
> > > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:[email protected]...
> > > > 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
> > > > news:%[email protected]...
> > > >> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > > > news:ue%[email protected]...
> > > >> > 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
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> >
> >
>
>