Sub CalculateAndDisplayLateMinutesByPerson()
Dim LastRow As Long
Dim TimeDifference As Double
Dim ws As Worksheet
Dim PersonMinutes As Object
Dim PersonName As Variant
Dim ResultMessage As String
' Create a dictionary to store minutes for each person
Set PersonMinutes = CreateObject("Scripting.Dictionary")
' Assume we are working with the first (or active) worksheet
Set ws = ThisWorkbook.Sheets(1)
' Find the last row with data in column H
LastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
' Loop through each row
On Error GoTo ErrorHandler
For i = 1 To LastRow
' Get the person's name from column A
PersonName = ws.Cells(i, 1).Value
' Check if column H indicates "迟到"
If ws.Cells(i, 8).Value = "迟到" Then
' Calculate the time difference in minutes
TimeDifference = (ws.Cells(i, 7).Value - TimeValue("9:00:00")) * 1440 ' 1440 is the number of minutes in a day
' Add to the total for this person if it's positive
If TimeDifference > 0 Then
If PersonMinutes.Exists(PersonName) Then
PersonMinutes(PersonName) = PersonMinutes(PersonName) + TimeDifference
Else
PersonMinutes.Add PersonName, TimeDifference
End If
End If
End If
Next i
' Prepare the result message
For Each PersonName In PersonMinutes.Keys
ResultMessage = ResultMessage & PersonName & ": " & Round(PersonMinutes(PersonName), 0) & " minutes" & vbCrLf
Next PersonName
' Display the results in a message box
MsgBox ResultMessage, vbInformation, "Late Minutes Calculation by Person"
Exit Sub
ErrorHandler:
MsgBox "Error on row " & i & ": " & Err.Description, vbCritical, "Error"
End Sub
