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
CodePudding user response:
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
CodePudding user response:
Looping Through the Items of a Dictionary
- Check out these two dictionary resources:
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
CodePudding user response:
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