COUNTIF criteria in VBA

  • Thread starter Thread starter whitehurst
  • Start date Start date
W

whitehurst

What vba function can I use that checks criteria much like SUMIF o
COUNTIF uses? In other words, say you want to write COUNTIF that onl
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
 
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
 
Thank you, I figured criteria expressions could be more complicated tha
just appending, but I guess that can't be!

Can anybody think of a way to write CountVIf without having to loo
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'
seem to work in UDFs - which sucks.

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