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

Use Evaluate

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

--

HTH

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


"xcelion" wrote in
message ...

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


--
xcelion
------------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=485839

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" wrote in
message ...

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


--
xcelion
------------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=485839

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" wrote in
message ...

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