Results 1 to 3 of 3

Thread: Macro to do Permutations by a number

  1. #1

    Macro to do Permutations by a number



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good morning everyone,

    I would like to do a macro that does permutations by a selected number producing "unique, random" combinations only pertaining to the inputted number in a cell in my workbook. I would like to control how may combinations the macro would also produce, telling the macro to give me only 200 combinations or so.

    I would input the number (9) I to a cell in my workbook thus the macro would only produce "unique,random" combinations pertaining to the inputted number.

    Examples.
    9-15-23-36-42-53
    9-21-36-48-50-51
    9-12-25-34-44-49

    I have a sample workbook.

    Thank you,
    Attached Files Attached Files

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    First should point out that even 200 permutations only represent a small fraction of the posible combinations.

    Below code should generate what you want
    Code:
    Option Explicit
    
    
    Sub RandomData(nRows As Byte, nCol As Byte, nMax As Byte, sStart As String, sOut As Range)
    Dim oRandom()
    Dim i As Byte, j As Byte
    Dim sTemp As String, sAll As String
    
    
        Randomize
    
    
        ReDim oRandom(1 To nRows, 1 To 2)
        For i = 1 To nRows
            Do
                oRandom(i, 1) = Format(sStart, "0") & "-"
                For j = 1 To nCol
                    Do
                        sTemp = Format(Int(nMax * Rnd) + 1, "0") & "-"
                    Loop Until InStr(oRandom(i, 1), sTemp) = 0
                    oRandom(i, 1) = oRandom(i, 1) & sTemp
                Next j
            Loop Until InStr(sAll, oRandom(i, 1)) = 0
            oRandom(i, 1) = Left(oRandom(i, 1), Len(oRandom(i, 1)) - 1)
            sAll = sAll & "|" & oRandom(i, 1)
        Next i
        
        sOut.Cells(1, 1).Resize(nRows, 1) = oRandom
        
    End Sub
    
    
    Sub testit()
        Call RandomData(220, 5, 53, "9", ActiveSheet.Range("B3"))
    End Sub

  3. #3
    Thank you I will give it a try

Posting Permissions

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