Need help building macro to do a date filter and count the results.

[jlroeder]

I don't have much experience using VB and I a confused on where to start. I would like to refresh my data and then do a date filter between two dates. I would like a pop up box appear to select the dates to look for and then have another box showing how many it counted. It needs to count how many times sr appears and how many times tc appears. Any help is greatly appreciated.

 

[Andrzejek]

Start your macro recorder, do all the steps, stop recording, look at the VBA code (Alt-F11)
Or if you really want to learn, do one step at the time, and look at the VBA code.

Ask questions here, but do your work first.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

You do realize that Excel has features like the Auto Filter that will do what you want to do with your date column.

Once filtered, the SUBTOTAL() function can return the count of visible cells.

Your sr & ct counts can also be returned by either the COUNTIFS() or SUMPRODUCT() functions.

 

[Gavona]

Another alternative is =DCOUNT(database,field,criteria)

Gavin

 

[jlroeder]

Ok so I have a user form with two date selection boxes and a run click button. It's supposed to filter the dates in colum O and then count how many times TC and SR shows in the filter. It then is to pop up in another window with the results.

This is what I have

Private Sub CommandButton1_Click()
Dim count As Integer
Dim count1 As Integer

ActiveSheet.ListObjects("Table_Query_from_TMS_ODBC").Range.AutoFilter Field:= _
15, Criteria1:=">=9/11/2014", Operator:=xlAnd, Criteria2:="<=9/18/2014"



Worksheets("Weekly Numbers").Select
Dim LR As Long, i As Long
count = 0
LR = Range("D" & Rows.count).End(xlUp).Row
For i = LR To 2 Step -1
If Range("SR" & i).Value = "TC" Then
count = count

End If
Next i
Dim L As Long, j As Long
count1 = 0
LT = Range("D" & Rows.count).End(xlUp).Row
For j = LT To 2 Step -1
If Not Range("TC" & j).Value = "TC" Then
count1 = count1

End If
Next j
Worksheets("Weekly Numbers").Select
MsgBox count1 & " Work Order(s) counted and " _
& count & " Trouble Order(s) counted"


My pop up window is showing 0 and 0 for my results. Where did I go wrong?

 

[SkipVought]

Do you mean...

Count = count + 1

? Cuz otherwise count & count1 NEVER change!

 

[SkipVought]

And to reiterate, I'd wager a paycheck that these counts could be calculated with either the COUNTIFS() or SUMPRODUCT() function quite simply!

 

[jlroeder]

It doesn't count the occurances of SR and TC in colum D for the date range. It seems to count all of them.

 

[SkipVought]

Are you checking to see if the row of the cell reference is hidden?

With Range("SR" & I)
   If .Value = "TC"  And Not .Hidden Then
      count = count + 1
   End if
End with

 

[Andrzejek]

You can help yourself by placing some breakpoints in your code, step thru the code and see what is going on.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[zelgar]

Try this revison of your code:

Private Sub CommandButton1_Click()
Dim count(2) As Integer
Dim iRow As Integer
Dim text as String

ActiveSheet.ListObjects("Table_Query_from_TMS_ODBC").Range.AutoFilter Field:= _
15, Criteria1:=">=9/11/2014", Operator:=xlAnd, Criteria2:="<=9/18/2014"



Worksheets("Weekly Numbers").Select
For x = 1 to 2
    text = Choose(x, "TC", "SR")
    count(x-1) = 0
    iRow = 2
    Do UntilIsEmpty(cells(4,iRow))
         if cells(4,iRow) = text then count(x-1) = count(x-1) + 1
         iRow = iRow + 1
    Loop
Next x

Worksheets("Weekly Numbers").Select
MsgBox count(1) & " Work Order(s) counted and " _
& count(0) & " Trouble Order(s) counted"