Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- xcelion ------------------------------------------------------------------------ xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287 View this thread: http://www.excelforum.com/showthread...hreadid=485839 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula Using WorksheetFunction
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula Using WorksheetFunction
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula Using WorksheetFunction
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula Using WorksheetFunction
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula Using WorksheetFunction
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula Using WorksheetFunction
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 -- xcelio ----------------------------------------------------------------------- xcelion's Profile: http://www.excelforum.com/member.php...fo&userid=1628 View this thread: http://www.excelforum.com/showthread.php?threadid=48583 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula Using WorksheetFunction
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)=strCr iteria,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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula Using WorksheetFunction
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" wrote in message ... 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)=strCr iteria,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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula Using WorksheetFunction
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 -- xcelion ------------------------------------------------------------------------ xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287 View this thread: http://www.excelforum.com/showthread...hreadid=485839 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming | |||
WorkSheetFunction.CountIf & WorkSheetFunction.SumIf with 2 conditions? | Excel Programming |