Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NEED TO COUNTIF WITH TWO CRITERIA | Excel Worksheet Functions | |||
COUNTIF criteria | Excel Worksheet Functions | |||
Countif with more than one criteria | Excel Discussion (Misc queries) | |||
Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions |