HI Domenic
Can u please please be kind enough and explain how the code works.
i really want to understand how the dict item etc...works
I really understand the way you explain things and would really appreciate if you could do that please
many thanks
A Dictionary object is an object that we create in order to store data so that we can refer to it when needed. The data stored is a key and item pair. This allows us to retrieve an item using it's key...
In this example, first we create an instance of the Dictionary object using this line of code...
Code:
[COLOR=#00008b]Set[/COLOR] oDict = CreateObject("Scripting.Dictionary")
Then we loop through the data so that two things happen. First, we fill the array aData with the category and amount. For the array, the first row contains the category and the second row contains the corresponding amount. So each category/amount pair will be placed in a subsequent column within the array aData. So the first category/amount pair are placed in the first column, the second pair in the second column, and so on...
Code:
c = c + 1
aData(1, c) = Rng(i, 3).Value
aData(2, c) = Rng(i, 2).Value
Then, at the same time, we want to store the category and column index in our Dictionary. The category being the key, and the column index being the item...
Code:
oDict.Add Rng(i, 3).Value, c
However, when we're looping through the data, we use the Exists method of the Dictionary object to see whether the key, in this case the category, exists before filling our array aData, using the following statement...
Code:
[COLOR=#00008b]If[/COLOR] [COLOR=darkblue]Not[/COLOR] oDict.Exists(Rng(i, 3).Value) [COLOR=darkblue]Then[/COLOR]
If it doesn't already exist, we go ahead and fill aData with the category and amount, while storing the category and column index in our Dictionary. If, however, the key or category already exists, we only want to update its corresponding amount in aData. And we can use the category (key) to retrieve the column index (item) from the Dictionary. With the column index, we can now update the amount for the category in the array aData.
This gives us our column index (item) for the category...
Code:
oDict.Item(Rng(i, 3).Value)
This refers to the amount corresponding to the category in aData...
Code:
aData(2, oDict.Item(Rng(i, 3).Value))