VBA: Add Item to Existing Key in Dictionary-CodePudding

I want to do something extremely simple and I still can't find the solution via Google. I want to add an item to an existing key in a VBA dictionary.

My Code:

Sub mymacro()

Set DICT = CreateObject("scripting.dictionary")

For i = 1 To 10
    key = Worksheets("Sheet1").Cells(i, "D").Value
    item = Worksheets("Sheet1").Cells(i, "L").Value
    DICT.Add key, item
    If DICT.Exists(key) Then
        DICT(key).Add item    '(<-- causes runtime error 424, object required)
    End If
Next i

For Each i In DICT.Items: Debug.Print i: Next

End Sub

This code however is giving me a Runtime Error 424, 'Object required' Any help is appreciated

CodePudding user response：

You can store Collection objects in the Dictionary's values. This allows the storage of multiple values per key. Something like:

Option Explicit

Sub mymacro()

Dim dict As Object
Set dict = CreateObject("scripting.dictionary")

Dim i As Long
Dim ColumnDKey As Variant
Dim ColumnLValue As Variant
Dim ColumnLValues As Collection

For i = 1 To 10
    ColumnDKey = Worksheets("Sheet1").Cells(i, "D").Value
    ColumnLValue = Worksheets("Sheet1").Cells(i, "L").Value
    If Not dict.Exists(ColumnDKey) Then
        dict.Add ColumnDKey, New Collection
    End If

    Set ColumnLValues = dict.Item(ColumnDKey)
    ColumnLValues.Add ColumnLValue
Next i

For Each ColumnDKey In dict.Keys
    Set ColumnLValues = dict.Item(ColumnDKey)
    
    For Each ColumnLValue In ColumnLValues
        Debug.Print ColumnLValue
    Next
Next

End Sub