Dictionary Object: Add range OR array as an item to a corresponding key?

[sccrsurfer]

Hi Guys, can I add a range or an array as an item to a corresponding key? Lets say I have a spreadsheet where Column A has values that populate my dictionary Keys and columns B:D contain the item. So if I used the "Add" method,it would look like this: D.Add Key:=Value Item:Values in the row where the key appears and columns B:D. The goal would be to fill a dictionary with keys in column A, loop through the unique keys, and on another worksheet, dump the items associated w/ each key. Is this possible?

 

[pgc01]

Hi

No problem in adding an array as a value.

For ex., run:

Sub test()
Dim dic As Object

Set dic = CreateObject("Scripting.Dictionary")
dic.Add "AAA", Array("Hi", "there", "!")

MsgBox Join(dic("AAA"))
End Sub

 

[sccrsurfer]

Thanks pgc01! For some reason when I hit {Enter} it won't start a new line so my posts will be a bit sloppy. These arrays will be dynamic arrays. So I'm also trying to figure out the best way to fill the array. Should I just do something like D.Add "AAA", Range("B" & row & ":D" & row)?

 

[pgc01]

Hi

You mean adding the values in the range?
Don't forget that if you read the values of a range you get a 2D array.

Sub test2()
Dim dic As Object
Dim lRow As Long
Dim v As Variant

lRow = 3
Range("B3") = "Hi"
Range("C3") = "there"
Range("D3") = "!"

Set dic = CreateObject("Scripting.Dictionary")

dic.Add "AAA", Range("B" & lRow & ":D" & lRow).Value

v = dic("AAA")

MsgBox v(1, 1) & " " & v(1, 2) & " " & v(1, 3)

End Sub

 

[pgc01]

P. S.

Since in this case your range has only 1 row you could also convert the array from a 2D to a 1D array before you add it to the dictionary.

 

[sccrsurfer]

Thanks, I like this idea.

P. S.

Since in this case your range has only 1 row you could also convert the array from a 2D to a 1D array before you add it to the dictionary.

 

[pgc01]

I'm glad it helped.