I have the following code:
Sub Generate_report()
Dim ws As Worksheet
Dim kamagiData As Worksheet
Dim incomesData As Worksheet
Dim i As Long, lst As Long
Dim currentRow As Long
Dim currentTime As Variant
Dim currentKamag As String
Dim currentHour As Long
Set ws = ActiveSheet
Set kamagiData = Sheets("Kamagi")
Set incomesData = Sheets("Dane wjazdy")
Set hours = CreateObject("Scripting.Dictionary")
Dim kamags As Dictionary
With kamagiData
lst = .Cells(.Rows.count, 1).End(xlUp).Row
For i = 2 To lst
Set kamags = New Dictionary
currentTime = .Cells(i, 11).Value
currentHour = Hour(currentTime)
currentKamag = .Cells(i, 14).Value
If Not hours.exists(currentHour) Then
hours.Add currentHour, kamags
End If
'If Not hours(currentHour).exists(currentKamag) Then
'hours(currentHour).Add currentKamag, 1
'Else
'hours(currentHour).Item(currentKamag) = hours(currentHour).Item(currentKamag) + 1
'End If
Debug.Print (currentHour)
Debug.Print (hours(currentHour))
Debug.Print (currentKamag)
'Debug.Print (hours(currentHour).Item(currentKamag))
Next i
End With
'Po utworzeniu odpowiednich słowników należy uzupełnić odpowiednie pola w tabeli, najpierw czyszcząc je z poprzednich wersji
'Dodać przycisk czyszczenia raportów
'Dodać instrukcję
'Obsłużyć liczenie wjazdów/wyjazdów z yardu
End Sub
Its operating principle was to be as follows: In a loop, I check the table rows in the Kamagi sheet, extract the time from the date entered in column K, and the driver number, contained in column N. Then I need to create a division thanks to which I will be assigned the number of occurrences of the driver number in a given hour. The idea is to create a dictionary in which the keys will be the extracted hours, and the values will be dictionaries containing driver - number of occurrences pairs. I have no problem creating a dictionary with hours, the problem starts when I try to add a new dictionary as a value to which I could add drivers and increase the counter of their occurrences. I am trying to do it in this line:
If Not hours.exists(currentHour) Then
hours.Add currentHour, kamags
End If
Unfortunately, no matter what I try, I keep getting errors of all kinds. I have no idea how to properly initialize the dictionary so that later in the code I can operate on the dictionary element as a separate dictionary. I would be very grateful for any help on this topic.
Sub GenerateReport()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim wsKamagi As Worksheet: Set wsKamagi = wb.Sheets("Kamagi")
Dim wsIncomes As Worksheet: Set wsIncomes = wb.Sheets("Dane wjazdy")
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
With wsKamagi
Dim LastRow As Long: LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Dim i As Long, cHour As Long, cKamag As String
For i = 2 To LastRow
cHour = Hour(.Cells(i, "K").Value)
If Not dict.Exists(cHour) Then
Set dict(cHour) = CreateObject("Scripting.Dictionary")
End If
cKamag = CStr(.Cells(i, "N").Value)
dict(cHour)(cKamag) = dict(cHour)(cKamag) + 1
Next i
End With
Dim oKey As Variant, iKey As Variant
Debug.Print "Hour", "Kamag", "Occur."
For Each oKey In dict.Keys
Debug.Print "Outer - " & oKey
For Each iKey In dict(oKey).Keys
Debug.Print , "Inner " & iKey, dict(oKey)(iKey)
Next iKey
Next oKey
' ???
'Po utworzeniu odpowiednich slowników nalezy uzupelnic odpowiednie pola w tabeli, najpierw czyszczac je z poprzednich wersji
'Dodac przycisk czyszczenia raportów
'Dodac instrukcje
'Obsluzyc liczenie wjazdów/wyjazdów z yardu
End Sub
Result
Hour Kamag Occur.
Outer - 12
Inner A 2
Inner C 1
Outer - 13
Inner B 2
Inner A 1
Inner C 1
Outer - 14
Inner B 1
Inner A 1
Inner C 1