CountA and Countif and Arrays
 

Hi

I found out the hard way (by searching the internet/newsgroups) that while
it
is possible to use the Count worksheet function with a declared array, as
follows:
iNewRowsRequired = WorksheetFunction.CountA(ColumnsInfo)

BUT it is not possible to use it with CountIf worksheet function.

Can someone help show me where I can find that Countif will not work with
declared arrays?

Many thanks

Tim

CountA and Countif and Arrays
 

Tim Childs wrote :
Hi

I found out the hard way (by searching the internet/newsgroups) that while it
is possible to use the Count worksheet function with a declared array, as
follows:
iNewRowsRequired = WorksheetFunction.CountA(ColumnsInfo)

BUT it is not possible to use it with CountIf worksheet function.

Can someone help show me where I can find that Countif will not work with
declared arrays?

Many thanks

Tim

This returns the number of cells that contain the value 4 in row3:

Debug.Print Application.WorksheetFunction.CountIf([3:3], 4)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

CountA and Countif and Arrays
 

Hi Garry
thanks for reply - I meant that the argument was a "proper" array, NOT a
range within a worksheet
any ideas?
thx
Tim

"GS" wrote in message ...
Tim Childs wrote :
Hi

I found out the hard way (by searching the internet/newsgroups) that
while it
is possible to use the Count worksheet function with a declared array, as
follows:
iNewRowsRequired = WorksheetFunction.CountA(ColumnsInfo)

BUT it is not possible to use it with CountIf worksheet function.

Can someone help show me where I can find that Countif will not work with
declared arrays?

Many thanks

Tim

This returns the number of cells that contain the value 4 in row3:

Debug.Print Application.WorksheetFunction.CountIf([3:3], 4)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

CountA and Countif and Arrays
 

"GS" wrote in message
...
Tim Childs wrote :
Hi

I found out the hard way (by searching the internet/newsgroups) that
while it
is possible to use the Count worksheet function with a declared
array, as
follows:
iNewRowsRequired = WorksheetFunction.CountA(ColumnsInfo)

BUT it is not possible to use it with CountIf worksheet function.

Can someone help show me where I can find that Countif will not work
with
declared arrays?

Many thanks

Tim

This returns the number of cells that contain the value 4 in row3:

Debug.Print Application.WorksheetFunction.CountIf([3:3], 4)



Which doesn't answer OP's question. I was unable to learn anything
other than to verify that Countif doesn't work with declared arrays, but
that Count does:

Option Explicit

Sub x()
Dim a, b, c
a = Range("A1:A17")
10 b = WorksheetFunction.CountA(a)
20 c = WorksheetFunction.CountIf(a, "???")

Stop
End Sub

will throw an error on line 20.

The activesheet contains:

now
is
the
time
for
all
good
men
to
come
to
the
aid


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

CountA and Countif and Arrays
 

Clif McIrvin wrote on 6/29/2011 :
"GS" wrote in message ...
Tim Childs wrote :
Hi

I found out the hard way (by searching the internet/newsgroups) that while
it
is possible to use the Count worksheet function with a declared array, as
follows:
iNewRowsRequired = WorksheetFunction.CountA(ColumnsInfo)

BUT it is not possible to use it with CountIf worksheet function.

Can someone help show me where I can find that Countif will not work with
declared arrays?

Many thanks

Tim

This returns the number of cells that contain the value 4 in row3:

Debug.Print Application.WorksheetFunction.CountIf([3:3], 4)



Which doesn't answer OP's question. I was unable to learn anything other
than to verify that Countif doesn't work with declared arrays, but that Count
does:

That is what the online help says! Not sure why anyone would expect
something other than that!

The solution is obvious to me:

If you need to use CountIf in code then pass it a range object and
criteria.

If you need to use CountA in code then pass it a range object OR an
array of values.

If you need to use both on the same data source, pass them both a
range object.


Option Explicit

Sub x()
Dim a, b, c

Set a = Range("A1:A17")
b = WorksheetFunction.CountA(a) '//ACCEPTS a range OR an array.

c = WorksheetFunction.CountIf(a, "???") '//REQUIRES range,criteria


Stop
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

CountA and Countif and Arrays
 

Tim Childs laid this down on his screen :
Hi

I found out the hard way (by searching the internet/newsgroups) that while it
is possible to use the Count worksheet function with a declared array, as
follows:
iNewRowsRequired = WorksheetFunction.CountA(ColumnsInfo)

BUT it is not possible to use it with CountIf worksheet function.

Can someone help show me where I can find that Countif will not work with
declared arrays?

Many thanks

Tim

Sorry Tim, you can find the answer in online help.

CountA accepts a range (object) or an array.

CountIf requires range(object),criteria.

Since both will work with a range object, if you need to work with both
then use an object variable OR use the Set statement to load the range
into a range object.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

CountA and Countif and Arrays
 

"GS" wrote in message
...
Clif McIrvin wrote on 6/29/2011 :
"GS" wrote in message
...
Tim Childs wrote :
[ ]

Can someone help show me where I can find that Countif will not
work with
declared arrays?

Many thanks

Tim

[ ]
That is what the online help says! Not sure why anyone would expect
something other than that!


Garry, that sent me back to the on-board help files, where I realized
that I didn't read them carefully earlier.

Tim, the answer to your question is, in fact, in the help text (internet
search not needed):

Both COUNT and COUNTA specify the argument data type as *Variant*, while
COUNTIF specifies the argument data type as *Range*.


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

CountA and Countif and Arrays
 

Clif McIrvin explained on 6/29/2011 :
"GS" wrote in message ...
Clif McIrvin wrote on 6/29/2011 :
"GS" wrote in message
...
Tim Childs wrote :
[ ]

Can someone help show me where I can find that Countif will not work
with
declared arrays?

Many thanks

Tim

[ ]
That is what the online help says! Not sure why anyone would expect
something other than that!


Garry, that sent me back to the on-board help files, where I realized that I
didn't read them carefully earlier.

Tim, the answer to your question is, in fact, in the help text (internet
search not needed):

Both COUNT and COUNTA specify the argument data type as *Variant*, while
COUNTIF specifies the argument data type as *Range*.

Clif, my focus was more on what arguments were required. While both
COUNT/COUNTA require variant data types (thus allowing arrays of
values), they both explicitly state that they will accept a range OR an
array.

COUNTIF explicitly states that it will accept a range and criteria.

Since online (built-in) help provides descriptive info as to what each
function 'supports' args-wise, I found Tim's Q odd in that he wanted
someone to show him where it states COUNTIF doesn't accept an array. -
Not trying to be a smartass or throw digs at anyone but the help NOT
stating that COUNTIF accepts arrays should have been
self-illuminating.<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

CountA and Countif and Arrays
 

"GS" wrote in message
...
[ ]

Since online (built-in) help provides descriptive info as to what each
function 'supports' args-wise, I found Tim's Q odd in that he wanted
someone to show him where it states COUNTIF doesn't accept an array. -
Not trying to be a smartass or throw digs at anyone but the help NOT
stating that COUNTIF accepts arrays should have been
self-illuminating.<g


Yup. and I missed it too. <sigh

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

CountA and Countif and Arrays
 

Clif McIrvin formulated the question :
"GS" wrote in message ...
[ ]

Since online (built-in) help provides descriptive info as to what each
function 'supports' args-wise, I found Tim's Q odd in that he wanted
someone to show him where it states COUNTIF doesn't accept an array. - Not
trying to be a smartass or throw digs at anyone but the help NOT stating
that COUNTIF accepts arrays should have been self-illuminating.<g


Yup. and I missed it too. <sigh

Don't feel badly, ..it happens to us all!<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

CountA and Countif and Arrays
 

GS wrote :
Tim Childs laid this down on his screen :
Hi

I found out the hard way (by searching the internet/newsgroups) that while
it
is possible to use the Count worksheet function with a declared array, as
follows:
iNewRowsRequired = WorksheetFunction.CountA(ColumnsInfo)

BUT it is not possible to use it with CountIf worksheet function.

Can someone help show me where I can find that Countif will not work with
declared arrays?

Many thanks

Tim

Sorry Tim, you can find the answer in online help.

CountA accepts a range (object) or an array.

CountIf requires range(object),criteria.

Since both will work with a range object, if you need to work with both then

Correction...
use a Variant variable and use the Set statement to load the range into
it.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

CountA and Countif and Arrays
 

hi to both

many thanks for comprehensive answer - I hope you are not despairing (of
me!). I had not spot that (IMHO subtle) difference in the Helpfile about the
arguments

Best wishes

Tim

"GS" wrote in message ...
GS wrote :
Tim Childs laid this down on his screen :
Hi

I found out the hard way (by searching the internet/newsgroups) that
while it
is possible to use the Count worksheet function with a declared array,
as
follows:
iNewRowsRequired = WorksheetFunction.CountA(ColumnsInfo)

BUT it is not possible to use it with CountIf worksheet function.

Can someone help show me where I can find that Countif will not work
with
declared arrays?

Many thanks

Tim

Sorry Tim, you can find the answer in online help.

CountA accepts a range (object) or an array.

CountIf requires range(object),criteria.

Since both will work with a range object, if you need to work with both
then

Correction...
use a Variant variable and use the Set statement to load the range into
it.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

CountA and Countif and Arrays
 

After serious thinking Tim Childs wrote :
many thanks for comprehensive answer - I hope you are not despairing (of
me!). I had not spot that (IMHO subtle) difference in the Helpfile about the
arguments

No problem, Tim! Though I won't declare that the built-in Help is the
last word on anything since it's often useless when it comes to some of
its examples/explanations. Thankfully that's on the lesser side of
things as Help is usually the 1st place I look for answers. Working
examples is 2nd place.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

CountA and Countif and Arrays
 

"GS" wrote in message
...
After serious thinking Tim Childs wrote :
many thanks for comprehensive answer - I hope you are not despairing
(of me!). I had not spot that (IMHO subtle) difference in the
Helpfile about the arguments

No problem, Tim! Though I won't declare that the built-in Help is the
last word on anything since it's often useless when it comes to some
of its examples/explanations. Thankfully that's on the lesser side of
things as Help is usually the 1st place I look for answers. Working
examples is 2nd place.



What Garry said <grin.

I have found lurking in these newsgroups to be extremely instructive, as
well.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

CountA and Countif and Arrays
 

Clif McIrvin laid this down on his screen :
I have found lurking in these newsgroups to be extremely instructive, as
well.

Ditto! I thought that goes without saying since here we are..!<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc