Retrive multiple items from dictionary

Hi Guys,

This is similar post to this one:

https://www.mrexcel.com/forum/excel-...ml#post4754332

but this is not cross posting.

my code is:

Please Login or Register  to view this content.

So as you can see we have one key: "Key1" and a few items attached to it (1,2,3,4).

How to loop and retrive all items from all keys within dictionary?

Best wishes,
Jacek Antek

Hi,
Try this way:

Please Login or Register  to view this content.

Thnank you kasan!

This is working.

What if you have:

Please Login or Register  to view this content.

2 keys with different collections?


Something like this?:

Please Login or Register  to view this content.

Best wishes,
Jacek Antek

How about:

Please Login or Register  to view this content.

Wow excellent! You are brilliant man!

One more question:

It is possible to add value to specific item attacked to key?:

Please Login or Register  to view this content.

So for example i would like to change value "3" in collection coll for value "2".

Or only one possible way is to remove items form key and add once again new collection into dictionary ?

Best wishes ,
Jacek Antek

I think it's not possible to do it like you wrote.
If it would be arrays (not collections) then I would do something like this (not tested):

Please Login or Register  to view this content.

Unfortunately this is not working with reference to coll ;/

Please Login or Register  to view this content.

I have checked if this will be working with arrays and yes.
If you have array in dictionary this code will work.

Jacek Antek

May be some other experts/gurus can give you solution for collections. Interesting for me too.

I was thinking about doing the class with array inside and reference to it.

But i can not do it unfortunately...


Jacek

Try change to

Please Login or Register  to view this content.

Thank you jindon but this code is already working good:

Please Login or Register  to view this content.

Now we are thinking how to pass collection to array from dictionary.

Jacek

In that case I would not use Collection.
see

Please Login or Register  to view this content.

Probably you will need to loop through collection and add each element to array.
Using function this may looks like:

Please Login or Register  to view this content.

Function:

Please Login or Register  to view this content.

Thank you Guys.

Maybe this is the solution for this.

jidon you are very close:

Please Login or Register  to view this content.

What if we have 2 keys here and I would like to refer only for one of them ?

Jacek Antek

You can change them to

Please Login or Register  to view this content.

And you can convert all the items in an array ONLY IF all the array is exactly the same size.

Please Login or Register  to view this content.

Ok I have found this !

The key code here is:

Please Login or Register  to view this content.

Thanks to it you can assign items to an array, add values within array and return it into dictionary !!!

Thank you veru much
Jacek

See my last post that you just missed.

Wow this is very advance solution. Thank you !

So classes are not neccessary here?

It is a pity because i would like to learn it from you ;-)


Best Wishes
Jacek Antek

I use Array, Dictionary, ArrayList, SortedList in my code, so if you search my posts, you will find many examples.

Hello,

I solved the problem,

I simply added one dictionary and after operations with array - I am clearing it.

my Code:

Please Login or Register  to view this content.

I think that is not the best solution for this,
maybe you Gurus have any other ideas ?

Best Wishes,
Jacek Antek

Anyone ?

Jacek

If you upload a small sample workbook that clearly shows the logic about before/after, I can comment.

I can not help reading the code that is not working...

Hi Guys,

sorry for late reply.

In attachment there is my macro (PIT-liczenie v3).

Steps which should be run:

1. Run module Main in PIT-liczenie v3.
2. Indicate two files - "2016-08-03" and "2016-12-02":
- macro will be go through all sheets in each workbook and search for number in column G,
- if number is find is adding it to dictionary with sum,
- if key exists we are get from dictionary array, makes changes (sums adding) and assign this array to dictionary,
- first workbook is going fine,
- problem occurs where we will go to second workbook - there is a problem with searching for key in dictionary.
As I wrote before - code

Please Login or Register  to view this content.

is assigning array to dictionary and later key within dictionary is not searched.
But why only within second workbook?

I have noticed that when I am not making changes with array and I am not passing it to dictionary, Type for specific item will remain "Variant/Object/Dictionary" and thanks to it key.exists method is working.
Maybe there is another way to assign array into dictionary as Type "Variant/Object/Dictionary"? Why if we have array items in dictionary with Type "Variant" method key.exists doesnt work?

Please help me Guys,
Best Wishes,
Jacek Antek

Is this what you are trying?

Please Login or Register  to view this content.

jindon,

Thank You very much !!!

I thought that i can VBA code but Looking at yours - I think that I can't this.

Yes, it is exactly what i Meant to do.

I have couple of questions about it:

1.

Please Login or Register  to view this content.

What this code do? It is creating for key (a(i, 3)) the dictionary yes?

2. This method:

Please Login or Register  to view this content.

What we are doing here?
I think that for this dictionary(dic) within dictionary(a(i, 3)) we are assigning item txt and adding to it sum?

3. For what is this "n"?

Please Login or Register  to view this content.

4. what means ": n = 0" in

Please Login or Register  to view this content.

Ufff your code is master code!

Thank you in advance for help,
Jacek Antek

Wow and this one is also very confusing for me:

Bez*tytu?u.jpg

What does it means?

Jacek

Wow and this one is also very confusing for me:

"GalembaJolanta"

This strange sign which means that there is more then one item...

What does it means?

Jacek

1

Please Login or Register  to view this content.

As you see dic is the main dictionary to store unique item from col.G.
Once it finds unique item, set "child" dictionary for its item. dic key:= col.G, item:= createobject("scripting.dictionary")
2)

Please Login or Register  to view this content.

txt= a(i,1) & chr(2) & a(i,2)
Why chr(2)?, make it safer when concatenate 2 strings with the character that we merely use.
e.g.
If string1 = "A" and string2 = "BC", it will also equivalent to string1="AB" and string2 = "C", both "ABC"...
Now, dic(a(i,3)) = dictionary only for a(i,3), so when it finds txt, it adds a(i,6) to its item.
dic(a(i,3)).Add txt, a(i,6) for the first time and dic(a(i,3))(txt) + 1(i,6) to add to its item.

3)

Please Login or Register  to view this content.

This is to count max number of keys(txt) for overall child dictionary to determine the enough size of 1st dimension of output array.

4) n=0 to initialize row counter for output array...

Further question?

Thank you jindon once more time for your help and support !

Now i am understanding all !!!!

And learnt a lot of thing about dictionaries and not only about them...

Thank you !

Best Regards,
Jacek Antek

Very nice and thanks for the rep.

Glad if it helps to widen your knowledge about coding.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Ok i will do that.

One more thing,

a = ws.Range("e2:j154").Value is changing a variable from long to array.
It is not better to dim a as variant at the beginning?

Why? It is standard? Maybe is the fastest solution?

Jacek

Of course.

Please Login or Register  to view this content.

Any variable without its data type is Variant type.