Array Formula Using WorksheetFunction

[xcelion]

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

 

[Bob Phillips]

Use Evaluate

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

--

HTH

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

 

[xcelion]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

Thanks Bob,

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

Iam also a regular reader of dicks-blog.com

The article on SUMPRODUCT on you site is really amazing

Hoping to gain more knowledge form you and forum

Thanks
Xcelio

 

[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




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

 

[Bob Phillips]

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]

Here is my data

and the Location is range defined for two areas under City and
Headcount for the areas under Headcount

That hardcoding of Location is not required ,i was testing the code


Survey 1 Survey 2
City Headcount City Headcount
NY 1 NY 1
NY 2 CA 1
TX 0 TX 1
NY 5 NY 1
TX 4 CA 1
TX 0 TX 0

I have similiar function wriiten for SUMIF and it's working fine


Code:
--------------------
Function MySumIf(rng As range, strCriteria As String, rng_sum As range) As Long
Dim sum As Long
For i = 1 To range("Location").Areas.Count
sum = sum + WorksheetFunction.SumIf(rng.Areas(i), strCriteria, rng_sum.Areas(i))
Next i
MySumIf = sum
End Function
--------------------




Thanks
Xcelion