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

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

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" <(E-Mail Removed)> wrote in message news:iufv7v$of1$(E-Mail Removed)...
> 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
>
>

"GS" <(E-Mail Removed)> wrote in message
news:iufv7v$of1$(E-Mail Removed)...
> 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 :-)

Clif McIrvin wrote on 6/29/2011 :
> "GS" <(E-Mail Removed)> wrote in message news:iufv7v$of1$(E-Mail Removed)...
>> 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

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

"GS" <(E-Mail Removed)> wrote in message
news:iug73j$hnb$(E-Mail Removed)...
> Clif McIrvin wrote on 6/29/2011 :
>> "GS" <(E-Mail Removed)> wrote in message
>> news:iufv7v$of1$(E-Mail Removed)...
>>> 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 :-)

Clif McIrvin explained on 6/29/2011 :
> "GS" <(E-Mail Removed)> wrote in message news:iug73j$hnb$(E-Mail Removed)...
>> Clif McIrvin wrote on 6/29/2011 :
>>> "GS" <(E-Mail Removed)> wrote in message
>>> news:iufv7v$of1$(E-Mail Removed)...
>>>> 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

"GS" <(E-Mail Removed)> wrote in message
news:iugaa9$625$(E-Mail Removed)...
[ ]
>
> 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 :-)

Clif McIrvin formulated the question :
> "GS" <(E-Mail Removed)> wrote in message news:iugaa9$625$(E-Mail Removed)...
> [ ]
>>
>> 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