| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
| CountIf and Sum Help??? | Excel Discussion (Misc queries) | |||
| edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Threaded Mode
