COUNTIF criteria in VBA
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 -- whitehurs ----------------------------------------------------------------------- whitehurst's Profile: http://www.excelforum.com/member.php...fo&userid=3454 View this thread: http://www.excelforum.com/showthread.php?threadid=55035 |
COUNTIF criteria in VBA
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 -- whitehurs ----------------------------------------------------------------------- whitehurst's Profile: http://www.excelforum.com/member.php...fo&userid=3454 View this thread: http://www.excelforum.com/showthread.php?threadid=55035 |
COUNTIF criteria in VBA
You have to loop thru independent cells, because you must evaluate condition
against each cell's contents. -- AP "whitehurst" a écrit dans le message de news: ... 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 |
COUNTIF criteria in VBA
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 |
All times are GMT +1. The time now is 11:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com