
Originally Posted by
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
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.
Bookmarks