Counter issues VBA

Question

Struggling with this counter...

I am going through multiple pages of info and trying to count the number of overdue tasks then inserting this info into a statistics page.

Sub data_input_overdue()

Dim rw As Long
Dim Counter As Long

Dim col As Long
col = CountMyCols("Stats")

Worksheets("Stats").Cells(2, col + 1).Value = "Overdue"

Counter = 0


For Each sht In ThisWorkbook.Sheets

    For i = 2 To CountMyRows(sht.Name)
        c_date = Range("E" & i)
        dueDate = CDate(c_date)
        If dueDate < Date And sht.Range("I" & i).Value = "No" Then
        Counter = Counter + CLng(1)
        Worksheets("Stats").Cells(i, col + 1).Value = Counter
    End If
    Next i
Next sht
End Sub

Public Function CountMyRows(SName As String) As Long
On Error Resume Next
With ThisWorkbook.Worksheets(SName)
    CountMyRows = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Column

Its not counting and going into the right places in the table as well. I want it to go into a column starting on the 3rd row. In the multiple sheets there is a mixture of completed and uncompleted tasks

Answer 1

I think this is what you are trying to do, but I'm not sure how you will identify each total with the applicable sheet.

Sub data_input_overdue()
    Dim c_date
    Dim dueDate As Date
    Dim i As Long
    Dim Counter As Long
    Dim cntSheet As Long

    Dim col As Long
    col = CountMyCols("Stats")

    Worksheets("Stats").Cells(2, col + 1).Value = "Overdue"

    cntSheet = 0

    For Each sht In ThisWorkbook.Worksheets
        If sht.Name <> "Stats" Then ' Don't process the Stats sheet
            cntSheet = cntSheet + 1
            'Reset counter at the start of each sheet
            Counter = 0
            For i = 2 To CountMyRows(sht.Name)
                c_date = sht.Range("E" & i)
                dueDate = CDate(c_date)
                If dueDate < Date And sht.Range("I" & i).Value = "No" Then
                    Counter = Counter + 1
                End If
            Next i
            'Update Stats sheet after finished counting
            Worksheets("Stats").Cells(2 + cntSheet, col + 1).Value = Counter
        End If
    Next sht

End Sub

Public Function CountMyRows(SName As String) As Long
    On Error Resume Next
    With ThisWorkbook.Worksheets(SName)
        CountMyRows = .Cells.Find(What:="*", _
                                  After:=.Range("A1"), _
                                  Lookat:=xlPart, _
                                  LookIn:=xlFormulas, _
                                  SearchOrder:=xlByRows, _
                                  SearchDirection:=xlPrevious, _
                                  MatchCase:=False).Row
    End With
End Sub

Public Function CountMyCols(SName As String) As Long
    On Error Resume Next
    With ThisWorkbook.Worksheets(SName)
        CountMyCols = .Cells.Find(What:="*", _
                                  After:=.Range("A1"), _
                                  Lookat:=xlPart, _
                                  LookIn:=xlFormulas, _
                                  SearchOrder:=xlByColumns, _
                                  SearchDirection:=xlPrevious, _
                                  MatchCase:=False).Column
    End With
End Sub

---

If I was doing this, I would probably put the sheet names in column A and the totals in column B, or start with the sheet names in column A, and then use that sheet name to decide which sheet to process when calculating the total to put on that row (rather than just looping through all the sheets in tab order).