count

[RobcPettit]

Hi, if ive selected range("a5") with vba and id like ti count how many
number = to 21 are in a2:a5 then move to a9 and again count 21s in
a6:a9 and continue moving down the column. I can move down ok, just
dont know how to count for 21, in every 4 cells. Its important that its
every 4 cells and not 21s in the whole colums, because these are dailly
results which collect 4 values each day. Any ideas appreciated.
Regards Robert

 

[Tom Ogilvy]

Sub ShowCnt()
Dim lastrow as Long, i as Long
Dim rng as Range
lastrow = cells(rows.count,1).End(xlup).row
for i = 2 to lastrow step 4
set rng = cells(i,4).Resize(4,1)
cnt = application.Countif(rng,21)
msgbox rng.Address & ": " & cnt
Next
End Sub

 

[RobcPettit]

Thanks Tom brilliant.
Regards Robert