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