need help on either doing a loop and counting specific values orusing subtotal function

[Mike Ruiz]

Hi all,

I have saw plenty of questions similiar to mine and i copied every single code snippet i could and tweeked it but i get the wrong value.

I have 2 worksheets(Raw data & Report). On the 'Report' worksheet which has summarized data i click an option button to filter the 'Raw data' worksheet. It works great! Now with that filter on say column 'BU' i have anothercolumn (AW) that has number values (Raw data worksheet).

I need to count those values in column 'AW'(e.g '>=30' can have 20 entries) on the Raw Data worksheet and report those numbers on the Report worksheet.

I can get it to count all values >=30 but it doesnt respect the filter and counts them all. I tried countif, subtotal, dcount, and looping statement.

I dont know which one to use and if im using it correctly.

Thanks in advance,
Mike

 

[Claus Busch]

Hi Mike,

Am Thu, 30 Jan 2014 12:46:17 -0800 (PST) schrieb Mike Ruiz:

I need to count those values in column 'AW'(e.g '>=30' can have 20 entries) on the Raw Data worksheet and report those numbers on the Report worksheet.

I can get it to count all values >=30 but it doesnt respect the filter and counts them all. I tried countif, subtotal, dcount, and looping statement.

try:

Sub Test()
Dim LRow As Long
Dim myCount As Long
Dim rngC As Range

With Sheets("Report")
LRow = .Cells(.Rows.Count, "BU").End(xlUp).Row
For Each rngC In .Range("AW2:AW" & LRow) _
.SpecialCells(xlCellTypeVisible)
If rngC >= 30 Then
myCount = myCount + 1
End If
Next
End With
MsgBox myCount
End Sub


Regards
Claus B.

 

[Mike Ruiz]

Hi Mike, Am Thu, 30 Jan 2014 12:46:17 -0800 (PST) schrieb Mike Ruiz: > I need to count those values in column 'AW'(e.g '>=30' can have 20 entries)on the Raw Data worksheet and report those numbers on the Report worksheet.. > > I can get it to count all values >=30 but it doesnt respect the filter and counts them all. I tried countif, subtotal, dcount, and looping statement. try: Sub Test() Dim LRow As Long Dim myCount As Long Dim rngC As Range With Sheets("Report") LRow = .Cells(.Rows.Count, "BU").End(xlUp).Row For Each rngC In .Range("AW2:AW" & LRow) _ .SpecialCells(xlCellTypeVisible)If rngC >= 30 Then myCount = myCount + 1 End If Next End With MsgBox myCount End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2

That worked!!!!!! Thanks