Help using Scripting Dictionary to sum values

hi,

I am enclosing an example macro to help explain what i am trying to do.

I thought the best approach is to use a scripting dictionary to do what i wanted but i am having problems implementing the code.

What i want to do:
On the sheet "Lookup_table" i have two cols of data. A uniquesubj value and the other col is just another designation for the same subj value.
On the sheet "ADMS_table" i have three cols of data. Col-A is a list of "uniquesubj" values similar to whats in col-A of the "Lookup_table" worksheet. In Col-B of the "ADMS_table" is a possible status value. Col-C on the "ADMS_Table" is a count of said status value. My goal is to reprodue the output shown on the worksheet "Output_results".

I am enclosing the code and information in the excel file.

If using a scripting dictionary is not the best way i am open to other alternatives.

Any help would be appreaciated.

thanks.

If you have any questions about anything just let me know and i will do my best to answer them.

GC excel thanks.

It looks like it works thanks. The only thing is that yours produces ALL of the status values

H NEW NI PD PPD Q QEP QTP R T O


where as i need to limit the status values to a dynamic list in the array. But i think i can use what you provided to figure it out. I will work on it more tomorrow......kinda tired now.

thanks. again. really, thanks!!!!!!!!

hope your still around to answer a few questions..........took a look at the code ............understand everything up to this point

can you explain this to me a little.........

Please Login or Register  to view this content.

thanks.....I will take a look at it once I get home. thanks.

GC thanks that does help. thanks.

ok....its tougher than i thought.

here is my problem. in the code you presented you counted ALL the status values. in my situation i just need a subset of the status values counted. The ones i need (which can change from macro run to run) is

"H"
"NEW"
"NI"
"Q"
"QEP"
"QTP"

i have reviewed the code you sent and i am trying to implement what I need but i am having trouble. I know that the status values i am to consider is stored in this array status_array

stat_array(1) = "H"
stat_array(2) = "NEW"
stat_array(3) = "NI"
stat_array(4) = "Q"
stat_array(5) = "QEP"
stat_array(6) = "QTP"

Any thoughts?

see module3.......it outputs all the status values and not just the subset i need

ok....i think i figured it out.....turns out the output was the issue

can you take a look and see if this is how you would do it......

Hi Welchs101,
maybe so

Please Login or Register  to view this content.

its been awhile since i posted this but i have another instance / use of something like this and i am reviewing some of the code and i had a question about the following section of code

Please Login or Register  to view this content.

i know from the thread that this is somehow a dicgtionary of dictionqary......can someone help me figure out whats going on? For example: i dont understand this section at all

Please Login or Register  to view this content.

is the .exists(ref) for dict2 or some other dictionary?

also, i am having a hard time figuring out how to see whats in the dictionary.....any ideas

Do you want it like this?

Please Login or Register  to view this content.

thanks Jindon. I was also trying to understand "what" the code was doing. I knew that it used a dictionary of dictionaries and i wanted to try and understand it a little more.

Like this statement

Please Login or Register  to view this content.

Does the ".item" part supposed to have "dict2" ie....is it referencing dict2?
Also, how does one see the dictionary of dictionaires to see what is in it?

It's not my code, so I will leave it to someone else.

jindon,

i understand.......but do you know how to "view" or see whats in a dictionary and/or a dictionary of dictionaires?

If you step through my code with Local Window open, you can see w(2) (child dictionary).

ok...i will try that.

whats the best way to step through the code.....should i put stops in it or something?

While you are in VBE, click somewhere on the code and hit F8.

As you hit F8, it will execute one line of the code.

thanks.........when looking at the locals window......what does a dictionary in dictionary look like?

See "myItems" in Local Window when it stops.

Please Login or Register  to view this content.