COUNTIF criteria in VBA

[whitehurst]

What vba function can I use that checks criteria much like SUMIF or COUNTIF uses? In other words, say you want to write COUNTIF that only includes visible cells...

Public Function CountVIf(rng As range, criteria As String)
Dim cell As range, cmd As String

For Each cell In rng
If cell.RowHeight <> 0 And cell.ColumnWidth <> 0 Then
cmd = "COUNTIF(" & cell.Address & ",""" & criteria & """)"
CountVIf = CountVIf + Evaluate(cmd)
End If
Next cell
End Function

How can I do this without having to rely on Evaluate("COUNTIF...."?

Thanks!

[Ardus Petus]

Public Function CountVIf(rng As Range, criteria As String) As Long
Dim cell As Range, cmd As String

For Each cell In rng
If cell.RowHeight <> 0 And cell.ColumnWidth <> 0 Then
cmd = "=" & cell.Address & criteria
If Evaluate(cmd) Then CountVIf = CountVIf + 1
End If
Next cell
End Function

HTH
--
AP

"whitehurst" <[email protected]> a
écrit dans le message de news:
[email protected]...
>
> What vba function can I use that checks criteria much like SUMIF or
> COUNTIF uses? In other words, say you want to write COUNTIF that only
> includes visible cells...
>
> Public Function CountVIf(rng As range, criteria As String)
> Dim cell As range, cmd As String
>
> For Each cell In rng
> If cell.RowHeight <> 0 And cell.ColumnWidth <> 0 Then
> cmd = "COUNTIF(" & cell.Address & ",""" & criteria & """)"
> CountVIf = CountVIf + Evaluate(cmd)
> End If
> Next cell
> End Function
>
> How can I do this without having to rely on Evaluate("COUNTIF...."?
>
> Thanks!
>
>
> --
> whitehurst
> ------------------------------------------------------------------------
> whitehurst's Profile:
> http://www.excelforum.com/member.php...o&userid=34544
> View this thread: http://www.excelforum.com/showthread...hreadid=550351
>
>

[whitehurst]

Thank you, I figured criteria expressions could be more complicated than just appending, but I guess that can't be!

Can anybody think of a way to write CountVIf without having to loop over each cell? I was wanting to use SpecialCells(xlCellTypeVisible) and then just pass the result range (which would have multiple areas) into EVALUATE(COUNTIF(newRange, criteria)), but specialcells doesn't seem to work in UDFs - which sucks.

Thanks.

[Ardus Petus]

You have to loop thru independent cells, because you must evaluate condition
against each cell's contents.

--
AP

"whitehurst" <[email protected]> a
écrit dans le message de news:
[email protected]...
>
> Thank you, I figured criteria expressions could be more complicated than
> just appending, but I guess that can't be!
>
> Can anybody think of a way to write CountVIf without having to loop
> over each cell? I was wanting to use SpecialCells(xlCellTypeVisible)
> and then just pass the result range (which would have multiple areas)
> into EVALUATE(COUNTIF(newRange, criteria)), but specialcells doesn't
> seem to work in UDFs - which sucks.
>
> Thanks.
>
>
> --
> whitehurst
> ------------------------------------------------------------------------
> whitehurst's Profile:
> http://www.excelforum.com/member.php...o&userid=34544
> View this thread: http://www.excelforum.com/showthread...hreadid=550351
>

[Gary''s Student]

The problem is not with SpecialCells, it is with COUNTIF(). Even on the
worksheet COUNTIF won't work on dis-joint ranges. That's why the
cell-by-cell approach.

In:
http://www.microsoft.com/communities...a-e20c1dc1c7ba

Bob Phillips gave a very clever solution to this.


--
Gary''s Student


"whitehurst" wrote:

>
> Thank you, I figured criteria expressions could be more complicated than
> just appending, but I guess that can't be!
>
> Can anybody think of a way to write CountVIf without having to loop
> over each cell? I was wanting to use SpecialCells(xlCellTypeVisible)
> and then just pass the result range (which would have multiple areas)
> into EVALUATE(COUNTIF(newRange, criteria)), but specialcells doesn't
> seem to work in UDFs - which sucks.
>
> Thanks.
>
>
> --
> whitehurst
> ------------------------------------------------------------------------
> whitehurst's Profile: http://www.excelforum.com/member.php...o&userid=34544
> View this thread: http://www.excelforum.com/showthread...hreadid=550351
>
>