Add Range to Dictionary

[30percent]

Hi,

how do I add a range to dictionary using VBA? As an example shown below, using Product as key and Order as value:

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Product[/TD]
[TD="class: xl63, width: 64"]Order[/TD]
[/TR]
[TR]
[TD="class: xl64"]Chair[/TD]
[TD="class: xl64"]10[/TD]
[/TR]
[TR]
[TD="class: xl64"]Table[/TD]
[TD="class: xl64"]5[/TD]
[/TR]
[TR]
[TD="class: xl64"]Pencil[/TD]
[TD="class: xl64"]20[/TD]
[/TR]
[TR]
[TD="class: xl64"]Pen[/TD]
[TD="class: xl64"]30[/TD]
[/TR]
[TR]
[TD="class: xl64"]Printer[/TD]
[TD="class: xl64"]1[/TD]
[/TR]
[TR]
[TD="class: xl64"]Phone[/TD]
[TD="class: xl64"]5[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[RoryA]

There are numerous examples on the forum. Here's some sample code from one such post:

Dim MyDict As Object, i As Long, MyVals As Variant

    Set MyDict = CreateObject("Scripting.Dictionary")
    
    MyVals = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    For i = 1 To UBound(MyVals)
        MyDict(MyVals(i, 1)) = MyVals(i, 2)
    Next i

I've assumed there would be no duplicate product names?

 

[30percent]

Hi @RoryA,

thank you!
There are no duplicate keys.

Wonder if it is possible to create a dictionary without using loop.

 

[RoryA]

No it is not. Why can’t you loop?

 

[JohnExcel222]

There are numerous examples on the forum. Here's some sample code from one such post:

Dim MyDict As Object, i As Long, MyVals As Variant

    Set MyDict = CreateObject("Scripting.Dictionary")
   
    MyVals = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    For i = 1 To UBound(MyVals)
        MyDict(MyVals(i, 1)) = MyVals(i, 2)
    Next i

I've assumed there would be no duplicate product names?

Hi Rory,
How do you print this dictionary on a worksheet?

 

[Rick Rothstein]

@JohnExcel222 - What exactly are you wanting to do? If all you want is to store and existing range in memory, maybe do some calculations or manipulations to the data and then print it back out to a worksheet, then storing that data in a VBA array would be more efficient than using a Dictionary.

 

[JohnExcel222]

@JohnExcel222 - What exactly are you wanting to do? If all you want is to store and existing range in memory, maybe do some calculations or manipulations to the data and then print it back out to a worksheet, then storing that data in a VBA array would be more efficient than using a Dictionary.

Hi Rick,
I would like to develop an order management / costs and invoicing management / tracker, with daily new work orders, constantly changing status, updates.... and I need to lookup into several tables to have a picture of what is going on ..... I try to get into dictionaries and arrays .... but is not easy. here is what I try to do .... (as a first step) .... Lookup in other sheets

 

[JohnExcel222]

@JohnExcel222 - What exactly are you wanting to do? If all you want is to store and existing range in memory, maybe do some calculations or manipulations to the data and then print it back out to a worksheet, then storing that data in a VBA array would be more efficient than using a Dictionary.

ah ok, I need to focus on arrays then

 

[Rick Rothstein]

ah ok, I need to focus on arrays then

Loading an array is nearly instantaneous. Assuming a Variant variable named Arr and the range A2:M99

Arr = Range("A2:M99").Value

You can then loop through the array by iterating a For..Next counter from 1 (arrays created this way always have a lower bound of 1) to UBound(Arr), do whatever manipulations you want and write the new values back to the original array, if appropriate, or to a new Variant variable ReDim'med to the size of the Arr array (remember, Arr is a two-dimensional array). You can then write the manipulated array (I'll use Arr for this example, but it could just as easily be the "new Variant variable" I mentioned a moment ago) back to whatever range you want (here, I start at Column P for example purposes, but you could write right over the original array if that is what is needed)...

Range("P2").Resize(UBound(Arr, 1), UBound(Arr, 2)) = Arr

Writing this array back is also nearly instantaneous as well.

 

[JohnExcel222]

Loading an array is nearly instantaneous. Assuming a Variant variable named Arr and the range A2:M99

Arr = Range("A2:M99").Value

You can then loop through the array by iterating a For..Next counter from 1 (arrays created this way always have a lower bound of 1) to UBound(Arr), do whatever manipulations you want and write the new values back to the original array, if appropriate, or to a new Variant variable ReDim'med to the size of the Arr array (remember, Arr is a two-dimensional array). You can then write the manipulated array (I'll use Arr for this example, but it could just as easily be the "new Variant variable" I mentioned a moment ago) back to whatever range you want (here, I start at Column P for example purposes, but you could write right over the original array if that is what is needed)...

Range("P2").Resize(UBound(Arr, 1), UBound(Arr, 2)) = Arr

Writing this array back is also nearly