Nur geleistete Stunden in der Zusammenfassung anzeigen

Private Sub UserForm_Activate()

    Dim ws As Worksheet

    Dim currentDate As Date

    Dim targetDate As Date

    Dim i As Long

    Dim j As Long

    Dim k As Long

    Dim rowIndex As Long

    Dim counts()



    Set ws = ThisWorkbook.Sheets("Projektplan")

    currentDate = Date

    

    With ListBox1

        .ColumnCount = 12

        .ColumnWidths = "100;30;30;30;30;30;30;30;30;30;30;30"

    End With

    

    For i = 7 To gLR

        If ws.Cells(i, "S").Value = "" Then

            If ws.Cells(i, "R").Value >= currentDate And ws.Cells(i, "D").Value = currentDate Then

                rowIndex = ListBox1.ListCount

                k = k + 1

                ReDim Preserve counts(1 To 12, 1 To k)

                counts(1, k) = ws.Cells(i, "B").Value

                For j = 2 To 12

                    If ws.Cells(i, j + 3) = "x" Then

                        counts(j, k) = Application.WorksheetFunction.CountIf(ws.Range("W" & i & ":NX" & i), j - 1)

                    End If

                Next j

                counts(11, k) = counts(11, k) + Application.WorksheetFunction.CountIf(ws.Range("W" & i & ":NX" & i), "P")

            End If

        End If

    Next i

    counts = Application.Transpose(counts)

    ListBox1.List = counts

      

End Sub