Macro to do Permutations by a number

B

b_rianv

New member
Joined
Jul 12, 2014
Messages
3
Reaction score
0
Points
0
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,
 

Attachments

  • Permutations by number.xlsm
    17.9 KB · Views: 9
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
 
You must log in or register to reply here.
Back
Top