Defined numbers - generator.

**LOxEN** · 11-01-2012, 08:45 AM

I have these 7 numbers: 1,3,3,3,4,5,9. I would need a generator that mixes up these numbers between them generating all possible combinations but not repeating the same combination so every combinations is unique.

Ex: 1333459, 3334591, 3345913, 3459133, 4591333, 4315933 an so on...

Thanks you for your time.

**Special-K** · 11-01-2012, 12:03 PM

This should work

Sub k1()
Dim i, l, m, n As String
Dim j, k As Integer
m = ""
i = "X1234567X"
For j = 7 To 1 Step -1
k = Int(Rnd() * j) + 1
l = Mid(i, k + 1, 1)
n = Left(i, k) & Right(i, Len(i) - (k + 1))
Debug.Print i, k, l, n
m = m + l
i = n
Next j
Range("A1").Value = m
End Sub

Result is pasted into cell A1

**LOxEN** · 11-02-2012, 08:37 AM

Unfortunately I get "The formula you typed contains an error.". Don't know what I'm doing wrong. Maybe you could attach the excel file to the reply?

Thank you again for your time and effort!

**Jakobshavn** · 11-02-2012, 10:16 AM

There are 840 unique permutations of the 7 values. This small macro, TEST, will list them in column A:

Sub TEST()
Dim j As Long, k As Long
k = 1
Application.ScreenUpdating = False
For j = 1333459 To 9543331
    If NumCheck(CStr(j)) Then
        Cells(k, 1) = j
        k = k + 1
        ' If k = 1000 Then Exit Sub
    End If
Next
Application.ScreenUpdating = True
End Sub
Function NumCheck(s As String) As Boolean
NumCheck = False
If InStr(s, "0") + InStr(s, "2") + InStr(s, "6") + InStr(s, "7") + InStr(s, "8") = 0 Then
    If Len(Replace(s, "1", "")) = 6 And Len(Replace(s, "4", "")) = 6 And Len(Replace(s, "9", "")) = 6 Then
        If Len(Replace(s, "3", "")) = 4 Then
            NumCheck = True
            Exit Function
        End If
    End If
End If
End Function

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro (TEST)
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Macros must be enabled for this to work!