Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    I have been trying for some time to figure out what VB code is needed to produce a list a result of a factorial calculation For instance how many different ways can you arrange 5 books. The answer of course the factorial of 5 which is 120. My problem is I then want to produce the list of all of those arrangements. The actual names would be cumbersome but an alphabet or numeric arrangement would work.

    From what I have seen on the web it looks as if a Knutf shuffle may be the way to go but so far have not been able to interpret what I have found so I can write into a code module on a spreadsheet. Any suggestions greatly appreciated.

    Thanks

    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the reply by "iliace" in Permutations/Combinations Problem in VBA - CodeGuru Forums.

    Note: the last line of the permuts procedure should be End Sub instead of End Function, otherwise it works OK.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Hi Hans

    Thanks once again that was a great referral works perfectly. However I must be asleep but it only works to 7 numbers or (alpha characters) . Past that I get a run time error where the results are being transposed into the worksheet. Are there some bounding limits I am missing?

    Thanks

    Peter

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can get a little bit further by changing all instances of "As Integer" in the code to "As Long".

    But keep in mind that factorial numbers increase extremely rapidly: 9! is larger than the number of available rows in Excel 97-2003, and 10! is larger than the number of rows in Excel 2007.


  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You can get a little bit further by changing all instances of "As Integer" in the code to "As Long".

    But keep in mind that factorial numbers increase extremely rapidly: 9! is larger than the number of available rows in Excel 97-2003, and 10! is larger than the number of rows in Excel 2007.

    Hi Hans

    Thank you , I tried that but the code will not let me change the array variable myArr to long. I get the message " Compile error Expected Array". This occurs on the array redim statement

    ReDim myArr(0 To Len(myStr) - 1).

    I am working on an allocation problem which has a max of 8 numbers so a factorial of 40320 well within the number of rows.

    Really appreciate your help

    Thanks

    Peter

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It works for me in the attached workbook...

    [attachment=86862:Perm.xls]

    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    It works for me in the attached workbook...

    [attachment=86862:Perm.xls]
    Hans

    Thanks, again it did not work but I suspected it was because I was running Excel 2000 so switched to 2003 and it worked so thank you once again.

    Peter

Posting Permissions

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