+ Reply to Thread
Results 1 to 7 of 7

Add & calculate Values in Scripting.Dictionary

  1. #1
    Registered User
    Join Date
    12-05-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    11

    Add & calculate Values in Scripting.Dictionary

    Dear Experts,
    Please refer the attached excel file.
    With the help of existing VBA codes, we are trying to derive no. of combinations for given sizes & then some calculations are made.

    If there are many sizes or the smaller sizes are there then the no. of combinations reaches to some thousands / lakhs.

    To reduce the time is it possible (instead of populating the data in excel cells with given VBA codes) to make all the entries & calculation in Scripting.Dictionary then just paste the data as shown in attached file.

    Please suggest.
    Thanks in advance....

    Add in Directory.xlsm
    Last edited by girish.talele; 12-17-2012 at 08:28 AM. Reason: more clarification about the problem

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Add & calculate Values in Scripting.Dictionary

    No need for dictionary

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    12-05-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Add & calculate Values in Scripting.Dictionary

    Dear Mike,
    Thank you very much....
    The time of calculation reduces for second part.
    Can you suggest some better method for below codes as populating the combinations is taking too much time. You can refer the attached file( Add in Directory-1.xlsm )where the combinations will be very high to 177,870 no.s.
    Please Login or Register  to view this content.
    Thanks once again for looking into.
    Last edited by girish.talele; 12-17-2012 at 09:46 AM.

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Add & calculate Values in Scripting.Dictionary

    You can try this. Still will take some time.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-05-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Add & calculate Values in Scripting.Dictionary

    Dear Mike,
    Sorry for delyed reply but you are simply great...

    With the help of your codes the results are coming in very less time.

    I have further reduced the entries in array by restricting via IF condition.
    Just one more help is required. When I run the program for below mentioned sizes the I am getting error - "Out of memory (Error 7)" might be due to the combinations are coming to some 1,331,000 no.s which array is not able to handle.

    I have tried to define array as Long or Double not get success.

    Main Size Size1 Size2 Size3 Size4 Size5 Size6 Size7
    1020 100 101 102 103 104 105 106

    Please suggest if there is any solution.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: Add & calculate Values in Scripting.Dictionary

    Hi -

    The error is in the tt variable, I changed it to 65536 since it will not goes to that number I guess.
    transfer some to variables so it will always access once.
    It Took : 11.8906 seconds from my machine
    Please Login or Register  to view this content.
    Regards,
    Event

  7. #7
    Registered User
    Join Date
    12-05-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Add & calculate Values in Scripting.Dictionary

    Dear Mike & Event,

    You guys are simply G R E A T....

    My issue is resolved. Got some thing more than waht I wanted....

    Thanks alot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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