Hello,
If your alphanumeric digits are 0..9 and A..Z you have 36 different
ones. So 4 digits can represent 36^4 = 1679616 different values.
Array enter into A1:A1000
=UniqRandInt(1679616)
and into B1
=Conv(A1,10,36,4)
and copy down to B1000.
My UDF UniqRandInt you can find here:
http://www.sulprobil.com/html/uniqrandint.html
[Set
#Const LATE_INITIALISATION = True
!]
The function Conv is copied below.
This approach will not work for 6 digits because 36^6 = 2176782336
which exceeds Excel's LONG representation. I would suggest to generate
the 6 digit alphanumeric numbers in a loop which will start over
whenever a previously used number is already in use.
Regards,
Bernd
Function Conv(Figure As String, FromBase As Integer, ToBase As
Integer, NumberOfDigits As Integer) As String
'(e-mail address removed), October 1999
'=conv(Figure,FromBase,ToBase,NumberOfDigits)
'Example: =conv(1234,6,16,6)
'If NumberOfDigits is set to 0 or fewer digits
'than are in the result, the result will be displayed without
'leading zeroes.
'The setup will convert a number from base 2-36
'to another base 2-36
'If the line "Figure = UCase(Figure)" is deleted, it's possible
'to place lower case letters in Digits to cover base 2-62.
'Please keep the above text, if you pass on this routine.
Dim Digits As String
Dim ToBaseTen As Long
Dim Dummy As Variant
Dim Counter As Integer
Dim Result As String
Conv = "Input error"
Digits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
If ToBase > Len(Digits) Then Exit Function
Figure = UCase(Figure)
For Counter = 1 To Len(Figure)
Dummy = Mid$(Figure, Counter, 1)
If InStr(Left$(Digits, FromBase), Dummy) = 0 Then
Exit Function
Else
ToBaseTen = ToBaseTen + (InStr(Digits, Dummy) - 1) *
(FromBase ^ (Len(Figure) - Counter))
End If
Next Counter
While ToBaseTen > 0
Result = Mid$(Digits, (ToBaseTen Mod ToBase) + 1, 1) & Result
ToBaseTen = Int(ToBaseTen / ToBase)
Wend
If NumberOfDigits = 0 Or NumberOfDigits < Len(Result) Then
Conv = Result
Else
Conv = Right$(String$(NumberOfDigits - Len(Result), "0") &
Result, NumberOfDigits)
End If
End Function
