Array Formula Using WorksheetFunction

Hi All,

I want to know how can i call a arrayformula using WorksheetFunction in VBA
For eg :
I need to call "=COUNT(IF(Location="NY",IF(Headcount,1)))" as an array formula using WorksheetFunction object
Is it possible ?

Thanks in advance
Xcelion

Use Evaluate

Activesheet.Evaluate = "=COUNT(IF(Location=""NY"",IF(Headcount,1)))"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"xcelion" <[email protected]> wrote in
message news:[email protected]...
>
> Hi All,
>
> I want to know how can i call a arrayformula using WorksheetFunction in
> VBA
> For eg :
> I need to call "=COUNT(IF(Location="NY",IF(Headcount,1)))" as an array
> formula using WorksheetFunction object
> Is it possible ?
>
> Thanks in advance
> Xcelion
>
>
> --
> xcelion
> ------------------------------------------------------------------------
> xcelion's Profile:
http://www.excelforum.com/member.php...o&userid=16287
> View this thread: http://www.excelforum.com/showthread...hreadid=485839
>

Hi Bob.

It's simply great.I was trying out for a solution in various ways.

Thanks Thanks alot
It's really new information for me
But one silly doubt
How did evaluate know it is a ArrayFormula ?.We are not specfying { or any thing else to denote it's an array formula

Thanks
Xcelion

That's a good question, and whilst I can't say that I know I could guess.

Excel obviously knows which function works on arrays, which on single cells.
Knowing that IF in itself works on a single cell, and that being used
against an array of cells, so it knows it must be an array formula.

Guess it is the same way that it often returns #VALUE if an array formula is
not array-entered in Excel itself.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"xcelion" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Bob.
>
> It's simply great.I was trying out for a solution in various ways.
>
> Thanks Thanks alot
> It's really new information for me
> But one silly doubt
> How did evaluate know it is a ArrayFormula ?.We are not specfying { or
> any thing else to denote it's an array formula
>
> Thanks
> Xcelion
>
>
> --
> xcelion
> ------------------------------------------------------------------------
> xcelion's Profile:
http://www.excelforum.com/member.php...o&userid=16287
> View this thread: http://www.excelforum.com/showthread...hreadid=485839
>

Hi Bob,

I am simply impressed by your answer.Thanks a lot.

Can you please advice so that i can also give superb answers like you

I really want to be a excel master like you.

What should i do ?

Thanks
Xcelion

I guess the best way to learn is two fold, get out and about in your
community and offer your services. That way you get to see what people want
of computers, and help them deliver it, In addition, join forums like this,
watch the questions and answers, join in when you can with a solution, and
keep on learning.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"xcelion" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Bob,
>
> I am simply impressed by your answer.Thanks a lot.
>
> Can you please advice so that i can also give superb answers like you
>
> I really want to be a excel master like you.
>
> What should i do ?
>
> Thanks
> Xcelion
>
>
> --
> xcelion
> ------------------------------------------------------------------------
> xcelion's Profile:
http://www.excelforum.com/member.php...o&userid=16287
> View this thread: http://www.excelforum.com/showthread...hreadid=485839
>

Thanks Bob,

Thanks for your advice.
I do visit forumns and answer questions possible for me

Iam also a regular reader of *****-blog.com

The article on SUMPRODUCT on you site is really amazing

Hoping to gain more knowledge form you and forum

Thanks
Xcelion

Hi Bob,

Your solution is working but iam unable to use it my case

Iam writting custom fuction where the parametes to this array formula are passed as arguments of the UDF like this

Please Login or Register  to view this content.

I should be able to call the fuction like this

=MyCountIf(Location,"NY",Headcount)

In this fuction iam getting error on the assignment rng.Areas(i)=strCriteria Type Mismatch

Can you please advice

Thanks
Xcelion

Bit difficult to be precise, without knowing the data etc.

A few thoughts.

Are you sure that rng_sum has those areas that you address in the code, you
do no checking?

Why do you hard code Location in the code? What is the relationship between
Location that you pass through the rng argument, and the range("Location")?

Show us what the data looks like.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"xcelion" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Bob,
>
> Your solution is working but iam unable to use it my case
>
> Iam writting custom fuction where the parametes to this array formula
> are passed as arguments of the UDF like this
>
>
>
>
> Code:
> --------------------
> Function MyCountIf(rng As range, strCriteria As String, rng_sum As
range) As Long
> Dim lCount As Long
>
> For i = 1 To range("Location").Areas.Count
> lCount = lCount +
ActiveSheet.Evaluate("=COUNT(IF(rng.Areas(i)=strCriteria,IF(rng_sum.Areas(i)
,1)))")
>
>
> Next i
> MyCountIf = lCount
> End Function
> --------------------
>
>
> I should be able to call the fuction like this
>
> =MyCountIf(Location,"NY",Headcount)
>
> In this fuction iam getting error on the assignment
> rng.Areas(i)=strCriteria Type Mismatch
>
> Can you please advice
>
> Thanks
> Xcelion
>
>
> --
> xcelion
> ------------------------------------------------------------------------
> xcelion's Profile:
http://www.excelforum.com/member.php...o&userid=16287
> View this thread: http://www.excelforum.com/showthread...hreadid=485839
>