# Thread: Macro to do Permutations by a number

1. ## Macro to do Permutations by a number

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,

2. 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. 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
•