+ Reply to Thread
Results 1 to 12 of 12

Combinations macro (need modifications)

  1. #1
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Combinations macro (need modifications)

    Hello guys,

    Here's my new problem.
    I found this VBA macro that I is very useful for my project.
    The problem - I must made one small change, but I do not know how to do.
    I will try to explain as best I can, because my English is not good.
    This VBA macro make combinations based on three constants and in this moment these three constants can only be set directly in it. I need these three constants can be set directly in the sheet.
    Please Login or Register  to view this content.
    Basically I need to set my list of these constants in sheet, and this macro to run automatically for the whole set / list of constants.

    1.jpg

    If something is not explained clearly enough, feel free to ask me.
    Thanks in advance for your help.
    Attached Files Attached Files
    Last edited by Anka; 05-01-2016 at 05:48 AM.
    Many thanks to all who have helped me.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Combinations macro (need modifications)

    Hi,

    Untested but presumably just replace the three Const declarations with n,q & p Dimmed as module level variables. i.e.


    Please Login or Register  to view this content.
    and then as the first lines of code in the procedure
    Please Login or Register  to view this content.
    using BC2:BC4 to hold the variables.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-14-2012
    Location
    Czech Republic
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Combinations macro (need modifications)

    Hello,
    it is interesting code...
    Richard, I think there has to be some loop as op needs combination for all rows...

    sysss

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Combinations macro (need modifications)

    @sysss,

    I had assumed (always potentially dangerous!) that the macro was working and the 'need to make a small change' was to be able to use variables rather than constants.

    Perhaps the OP will expand on this.

  5. #5
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Combinations macro (need modifications)

    Thanks Richard Buttrey for your response.
    As usual... I forgot to specific/explain good, some points.
    I noticed that the screenshot I posted is not quite clear enough.
    So, I want to set my list just like in the attached picture.
    I want to put in column W range W3 & last row, the n list (total number of elements)
    I want to put in column X range X3 & last row, the q list (number of groups)
    I want to put in column Y range Y3 & last row, the p list (number of elements per group)
    Output of results start in column AS.

    1.jpg

    At this point, to get the result from the image, I need to change manually four times these constants and run for four times the macro.
    For this reason I need to set a list (this list may vary in number of rows), and I want to run the macro just one time to obtain that result.

    I apologize for wrong or incomplete explanations

  6. #6
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Combinations macro (need modifications)

    I expose my problem in another forum where I found "the owner" of this macro.
    Link to Cross-Post: Here

  7. #7
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Combinations macro (need modifications)

    No one can help me ?

  8. #8
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Combinations macro (need modifications)

    Maybe today is my lucky day...

  9. #9
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Combinations macro (need modifications)

    Bump ! No one ??

  10. #10
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Combinations macro (need modifications)

    OK. Try this. see the attachment.

    Just fill in the yellow boxes but note that the q value is the upper limit and the code will cycle from 1 through to the upper limit of q giving all the combinations in one hit. The result will be placed in A1 down and across depending on the size of the array.

    And for those interested in the code....

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Happy with my advice? Click on the * reputation button below

  11. #11
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Combinations macro (need modifications)

    Sorry for delayed response, Crooza, but yes that's it !!
    Thank you very much.

  12. #12
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Combinations macro (need modifications)

    No worries. Glad it worked

+ 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. Modifications to code
    By exlecel123 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-26-2013, 06:12 AM
  2. [SOLVED] File won't open in XLSTART after modifications in macro code are made
    By HitTheEXCELerator in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-04-2013, 06:14 AM
  3. Macro that allows specific modifications, then protects the worksheet again?
    By cheddarthief in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2011, 02:18 PM
  4. Macro Modifications: Excel to Powerpoint
    By cr_cline in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2011, 01:28 PM
  5. Add-in modifications
    By Jacques Grobler in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-13-2011, 02:50 AM
  6. PivotTable modifications via VBA
    By fecurtis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-15-2008, 04:04 PM

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