CountA and Countif and Arrays

[Tim Childs]

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

 

[GS]

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)

 

[Tim Childs]

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

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

 

[Clif McIrvin]

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

 

[GS]

Clif McIrvin wrote on 6/29/2011 :

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

 

[GS]

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.

 

[Clif McIrvin]

Clif McIrvin wrote on 6/29/2011 :

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*.

 

[GS]

Clif McIrvin explained on 6/29/2011 :

Clif McIrvin wrote on 6/29/2011 :

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>

 

[Clif McIrvin]

[ ]

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>

 

[GS]

Clif McIrvin formulated the question :

Yup. and I missed it too. <sigh>

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

 

[GS]

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.

 

[Clif McIrvin]

After serious thinking Tim Childs wrote :

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.

 

[GS]

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>