+ Reply to Thread
Results 1 to 23 of 23

Help using Scripting Dictionary to sum values

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    Re: Help using Scripting Dictionary to sum values

    Hi welchs101,

    Try this code and let me know if it works as you want...
    Please Login or Register  to view this content.
    GC Excel - My blog

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

    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!!!!!!!!

  4. #4
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    Re: Help using Scripting Dictionary to sum values

    Hi,

    One other "non-VBA" option would be to use a pivot table.
    Add a column in your ADMS_Table sheet with a VLOOKUP function to get the subject ID, then do a Pivot table from there.
    You can even filter the status to show.

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

    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.

  6. #6
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    Re: Help using Scripting Dictionary to sum values

    Hello,


    I added more comments in the code. Let me know if it helps or not.

    The key point is that we store a dictionary within each element of the main dictionary...


    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

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

  8. #8
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

    GC thanks that does help. thanks.

  9. #9
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

    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?

  10. #10
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

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

  11. #11
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

    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......
    Attached Files Attached Files

  12. #12
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,376

    Re: Help using Scripting Dictionary to sum values

    Hi Welchs101,
    maybe so
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

    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

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,366

    Re: Help using Scripting Dictionary to sum values

    Do you want it like this?
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

    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?

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,366

    Re: Help using Scripting Dictionary to sum values

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

  17. #17
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

    jindon,

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

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,366

    Re: Help using Scripting Dictionary to sum values

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

  19. #19
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

    ok...i will try that.

  20. #20
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

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

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,366

    Re: Help using Scripting Dictionary to sum values

    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.

  22. #22
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,557

    Re: Help using Scripting Dictionary to sum values

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

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,366

    Re: Help using Scripting Dictionary to sum values

    See "myItems" in Local Window when it stops.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using Dictionary in VBA scripting
    By vnzerem in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-04-2014, 02:54 PM
  2. Scripting Dictionary
    By Tendla in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2013, 05:41 AM
  3. [SOLVED] Add & calculate Values in Scripting.Dictionary
    By girish.talele in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-21-2012, 06:32 AM
  4. [SOLVED] Scripting Dictionary
    By thisisgerald in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-18-2012, 01:30 PM
  5. [SOLVED] Scripting Dictionary help
    By williams485 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-13-2012, 08:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1