Generating of unique random set length numerical codes

[coreytroy]

I am after a way to have a cell highlighted to input the code length, between 4 - 7 digits say cell A1
Each code can only use a numerical value between 0 - 9, and not use the same digit more than once.
Eg. 6 digit code could be 371409, but could not be something like 374380, as 3 is used twice.

I would like a random list generated down column A from A2:A1000.

How would this be accomplished?

[mehmetcik]

I know that there is probably an array formula to do this.

But my prefferred route would be a custom Macro.

It will take ten minutes to write. BRB

Function RandCode()

Codestring = "0123456789"
Output = ""

10  If Len(Codestring) = 0 Then GoTo 200
If Len(Codestring) = 1 Then Output = Output & Codestring: GoTo 200
pos = Application.RandBetween(1, Len(Codestring))
Output = Output & Mid(Codestring, pos, 1)
Codestring1 = Left(Codestring, pos - 1)
Codestring2 = Right(Codestring, Len(Codestring) - pos)
Codestring = Codestring1 & Codestring2
GoTo 10

200 RandCode = "'" & Output

End Function

[kalak]

vba macro

Sub ddmdm()
Dim b() As Boolean, j As Long, x As Long, y
If (Len(Range("A1") > 0)) * (Len(Range("A1") < 10)) Then
For j = 2 To 1000
    y = vbNullString
    ReDim b(9)
    Do
        x = Int(Rnd * 10)
        If Not b(x) Then y = y & x: b(x) = True
    Loop Until Len(y) = Range("A1").Value
    Range("A" & j) = "'" & y
Next j
End If
End Sub

[coreytroy]

I know that there is probably an array formula to do this.

But my prefferred route would be a custom Macro.

It will take ten minutes to write. BRB

Function RandCode()

Codestring = "0123456789"
Output = ""

10  If Len(Codestring) = 0 Then GoTo 200
If Len(Codestring) = 1 Then Output = Output & Codestring: GoTo 200
pos = Application.RandBetween(1, Len(Codestring))
Output = Output & Mid(Codestring, pos, 1)
Codestring1 = Left(Codestring, pos - 1)
Codestring2 = Right(Codestring, Len(Codestring) - pos)
Codestring = Codestring1 & Codestring2
GoTo 10

200 RandCode = "'" & Output

End Function

Thank you very much for the reply, and workbook.
Seems to be te trick.
Modified to the below to suit a set code length:

Function RandCode()

Codestring = "0123456789"
Output = ""

10  If Len(Codestring) = 0 Then GoTo 200
If Len(Codestring) = 1 Then Output = Output & Codestring: GoTo 200
pos = Application.RandBetween(1, Len(Codestring))
Output = Output & Mid(Codestring, pos, 1)
Codestring1 = Left(Codestring, pos - 1)
Codestring2 = Right(Codestring, Len(Codestring) - pos)
Codestring = Codestring1 & Codestring2
GoTo 10

200 RandCode = Left("'" & Output, Sheet1.Range("B2"))

End Function

ONLY issue i am having, is that any changes to other cells in the sheet, changes the codes each time.

[mehmetcik]

If the cells are formatted as text then line 200 can be modified to:

200 RandCode = Output

[mehmetcik]

Try this version.

Two Changes
1. it stops the UDF working if B2 is changed to 0
2. it stops picking numbers if it has the number of digits specified by B2
[ Similar to your modification, but why pick 5 digits if you are going to ditch them?]

Function RandCode()
temp = Range(Application.Caller.Address).Text
If Range("B2") = 0 Then RandCode = Range(Application.Caller.Address).Text: GoTo 300
Codestring = "0123456789"
Output = ""

10  If Len(Codestring) = 10 - Sheet1.Range("B2") Then GoTo 200
If Len(Codestring) = 1 Then Output = Output & Codestring: GoTo 200
pos = Application.RandBetween(1, Len(Codestring))
Output = Output & Mid(Codestring, pos, 1)
Codestring1 = Left(Codestring, pos - 1)
Codestring2 = Right(Codestring, Len(Codestring) - pos)
Codestring = Codestring1 & Codestring2
GoTo 10

200 RandCode = "'" & Output
300 'Quit

End Function