countif in vba
I am impressed, very few lines !
"Dana DeLouis" wrote:
Hi. This isn't complete, but it may give you some ideas in the future...
Sub Demo()
Dim Cell As Range
Dim Rng As Range
Dim d1, d2
Set Rng = Range("_FilterDatabase")
Set Rng = Intersect(Rng, Columns("F:F"))
Set Rng = Rng.SpecialCells(xlCellTypeVisible)
For Each Cell In Rng.Cells
Select Case Cell.Value
Case "Dept1": d1 = d1 + 1
Case "Dept2": d2 = d2 + 1
End Select
Next Cell
End Sub
--
HTH. :)
Dana DeLouis
Windows XP, Office 2003
"Duncan" wrote in message
oups.com...
Quiller,
Many thanks, this is brilliant, I didnt know you could use select case
within a with and after a then, im learning all of this still!
Many thanks again. (ill post it below)
Duncan
With Worksheets("Sheet1")
Lastrow2 = .Cells(Rows.Count, "H").End(xlUp).Row
v1 = 0
v2 = 0
v3 = 0
v4 = 0
For J = 2 To Lastrow2
vdept = Range("F" & J).Value
If .Rows(J).Hidden = False Then
Select Case vdept
Case "dept1"
v1 = v1 + 1
Case "dept2"
v2 = v2 + 1
Case "dept3"
v3 = v3 + 1
Case "dept4"
v4 = v4 + 1
End Select
End If
Next J
End With
dept1text.Value = v1
dept2text.Value = v2
dept3text.Value = v3
dept4text.Value = v4
|