CountA and Countif and Arrays

T

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
 
G

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)
 
T

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

GS said:
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
 
C

Clif McIrvin

GS said:
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
 
G

GS

Clif McIrvin wrote on 6/29/2011 :
GS said:
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
 
G

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

Clif McIrvin

GS said:
Clif McIrvin wrote on 6/29/2011 :
GS said:
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*.
 
G

GS

Clif McIrvin explained on 6/29/2011 :
GS said:
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>
 
C

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

Clif McIrvin

GS said:
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.
 
G

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>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top