Option Explicit
Public Sub Example()
Dim wks As Excel.Worksheet
Dim objResult As Object
Set wks = ThisWorkbook.Worksheets("Januar")
If GetTotalHours(wks, objResult) > 0 Then
GoSub DebugPrintTotalHours
End If
Exit Sub
DebugPrintTotalHours:
Dim vntPerson As Variant
Debug.Print "['"; wks.Name; "']"
For Each vntPerson In objResult.Keys
Debug.Print " * '"; vntPerson; "':"; Tab(25); CStr(objResult(vntPerson)); " hours"
Next
Return
End Sub
Public Function GetTotalHours(ByVal MonthWorksheet As Excel.Worksheet, ByRef TotalHoursByPerson As Object) As Long
Const C_FIRST_ROW As Long = 2
Dim objDic As Object
Dim rngRow As Excel.Range
Dim strFullName As String
Set objDic = CreateObject("Scripting.Dictionary")
objDic.CompareMode = VbCompareMethod.vbTextCompare
With MonthWorksheet
For Each rngRow In .Range(.Cells(C_FIRST_ROW, "A"), .Cells(.Rows.Count, "A").End(xlUp))
strFullName = Trim$(.Cells(rngRow.Row, "A").Value)
If Len(strFullName) > 0 Then
objDic(strFullName) = CDbl(objDic(strFullName)) + CDbl(.Cells(rngRow.Row, "J").Value)
End If
Next
End With
Set TotalHoursByPerson = objDic
GetTotalHours = TotalHoursByPerson.Count
End Function