countif in vba
 

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

countif in vba
 

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" wrote:

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

countif in vba
 

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

countif in vba
 

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

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