Dictionary looping on values per key

user_17928541 0
Viewed 72

I have the code below to create a dictionary and assign values from 3 columns (i,1) key, and array of dates from getdates() from startdate(i,2) and enddate(i,3).

My goal is to print each date by key (there can be multiple dates for 1 key) but the code below is giving me a type mismatch error.

Sub Test_Dates()
    Dim TESTWB As Workbook
    Dim TESTWS As Worksheet

    Set TESTWB = ThisWorkbook
    Set TESTWS = TESTWB.Worksheets("TEST")

    Dim Dict As New Scripting.Dictionary

    For i = 2 To TESTWS.Cells(1, 1).End(xlDown).Row
        Dict.Add TESTWS.Cells(i, 1).Value, getDates(TESTWS.Cells(i, 2), TESTWS.Cells(i, 3))  
    Next i

    For Each Key In Dict.Keys
        Dim idate As Variant
        For Each idate In Dict.Items
            Debug.Print idate 'this line is where the error is
        Next idate
    Next Key
End Sub

This is get dates function to get array of dates between start and end

Function getDates(ByVal StartDate As Date, ByVal EndDate As Date) As Variant
    Dim varDates()      As Date
    Dim lngDateCounter  As Long

    ReDim varDates(0 To CLng(EndDate) - CLng(StartDate))

    For lngDateCounter = LBound(varDates) To UBound(varDates)
        varDates(lngDateCounter) = CDate(StartDate)
        StartDate = CDate(CDbl(StartDate) + 1)
    Next lngDateCounter

    getDates = varDates
End Function
vba excel date dictionary
3 Answers

Instead of 2 loops through your dictionary you can just use one

Dim i As Long
For i = 0 To Dict.Count - 1
    Debug.Print Dict.Keys(i),      Join(Dict.Items(i), "|")
Next i

alternatively if you need each date as single item, you need 2 loops

Dim i As Long
For i = 0 To Dict.Count - 1
    Dim DateItem As Variant
    For Each DateItem in Dict.Items(i)
        Debug.Print Dict.Keys(i),      DateItem 
    Next DateItem
Next i

A good source for working with dictionaries: Excel VBA Dictionary – A Complete Guide

user_32196130

Looping Through the Items of a Dictionary

To loop through each item of a dictionary and concatenate the elements of the item's array use:

Dim Arr As Variant
For Each Arr In Dict.Items
    Debug.Print Join(Arr, ", ")
Next Arr

To loop through each element of a dictionary containing zero-based arrays use:

Dim Arr As Variant
Dim i As Long
For Each Arr in Dict.Items
    For i = 0 To Ubound(Arr)
        Debug.Print Arr(i)
    Next i
Next Arr

Alternatively, use a double For...Each loop:

Dim Arr As Variant
Dim Item As Long
For Each Arr in Dict.Items
    For Each Item in Arr
        Debug.Print Item
    Next Item
Next Arr
user_98140690

I found that adding another loop on idate does the trick:

For Each Key In Dict.Keys

    Dim idate As Variant

    For Each idate In Dict.Items

        Dim bdate As Variant
        
        For Each bdate In idate
        
        Debug.Print bdate
    
        Next bdate
    Next idate
Next Key
user_179285410
Related