countif in vba

[Duncan]

Hi all,

Im using this from a previous sub and wanting to modify it to count
cells that have a set value, i got this loop from a helpful person on
here so i am having trouble getting my head around it enough to modify
it!.

Basically it is looking for non-hidden as the range is autofiltered, i
will have loads on entries and i want to split them into amounts in my
textboxes depending on their area.

(this is what i already have below)

With Worksheets("Sheet1")
Lastrow2 = .Cells(Rows.Count, "H").End(xlUp).Row
s2 = 0
For j = 2 To Lastrow2
If .Rows(j).Hidden = False Then s2 = s2 + 1
Next j
End With
textbox1.Value = s2

I want to modify this so it says if column F = "Department1" then count
it, then I can replicate this 4 times for each differant department and
putting the amount in differant textboxes.

Can anybody help me?

Many thanks in advance

Duncan

 

[Guest]

You need a new block of logic contained within the if statement (assuming
you still only want to count the non hidden entries):

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 "Department1"
v1 = v1 + 1
Case "Department2"
v2 = v2 + 1

{... etc ...}

End Select
End If
Next J

 

[Duncan]

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

 

[Dana DeLouis]

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

 

[Guest]

I am impressed, very few lines !

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