CODEWITHSUNDEEP
vba
CooperDC
CooperDC

Reputation: 3

Dictionary.Item returns collection but Dictionary.Item.Add adds new collection item to every key instead of specified key

I am trying to create a data structure in which a dictionary stores collections assigned to a key as a double. Each collection contains further array variants also. I am looping through rows in worksheet and adding certain values in each row to its associated collection for further manipulation later.

When I am adding data from a row to a collection, whether it belongs in a new collection--ergo a new key value pair in the dictionary--or simply just added to an existing collection, the data in the format of an array variant is being added to every key in the dictionary. Is somebody able to identify my problem?

          For Each row In Selection.Rows 'Loop through each row in chunks of 5000
    
                Dim NewInv(0 To 1) As Variant
    
                If MasterDict.Exists(row.Cells(3).Value) Then
                    NewInv(0) = row.Cells(15).Value
                    NewInv(1) = row.Cells(15).EntireRow.Address
                    MasterDict.Item(row.Cells(3).Value).Add (NewInv)
                    'for some reason the line above is adding the array variant to every collection assigned to every key, not just the specified key.
                Else
                    Dim NewAcct As New Collection
                    NewInv(0) = row.Cells(15).Value
                    NewInv(1) = row.Cells(15).EntireRow.Address
                    NewAcct.Add (NewInv)
                    MasterDict.Add Key:=row.Cells(3).Value, Item:=NewAcct
                End If
         Next

In the code above MasterDict is the dictionary in question.

Thank you for your response.

Upvotes: 0

Views: 45

Answers (1)

freeflow
freeflow

Reputation: 4355

You are making a fundamental error. You only have one NewInv array. Even though you change the values of the individual items this does not make it a new array thus during the loop the reference is to NewInv only and consequently only the last values assigned to NewInv will be visible in each item. To do what I think you intended you need to revise your code as follows

For Each Row In Selection.Rows 'Loop through each row in chunks of 5000
    
    If Not MasterDict.Exists(Row.Cells(3).Value) Then

        MasterDict.Add Key:=Row.Cells(3).Value, Item:=New Collection
        
    End If
        
    MasterDict.Item(Row.Cells(3).Value).Add Array(Row.Cells(15).Value, Row.Cells(15).EntireRow.Address)
                
Next

Upvotes: 1

Related Questions