5-digit Random Unique Alphanumeric Code

Here you go. You didn't specify where you wanted the output, nor how many codes you want, so this code generates 1 code and puts it in a messagebox.

As for not creating duplicates, this code doesn't address this for the same omissions mentioned above. However, in generating just 2 codes, there is a ~1 in 1.75 million chance that the codes would be the same, so really I'm not sure that duplicate checking is really necessary unless you're generating a massive amount of codes.

However, if you do specify how you want the output, checking for duplicates would be fairly easy with .Find

Sub mcodden()
code = ""
For i = 1 To 5
    If i Mod 2 = 0 Then
        code = code & Int((9 - 0 + 1) * Rnd)
    Else
        code = code & Chr(Int((122 - 97 + 1) * Rnd + 97))
    End If
Next
MsgBox code
End Sub

Hello walruseggman,

An easy method of avoiding duplicates is to add each code to a Collection. The collection object will generate a trappable error if the code already exists. The collection items can then be retrieved later.

DUPLICATE:

http://www.excelforum.com/excel-prog...eric-code.html

OP - please don't duplicate questions.

Hi mcodden,

The following Macro should help you get started.

Sub GenerateUniqueRandomValue()

  Dim ws As Worksheet

  Dim r As Range
  Dim iLastRow As Long
  Dim x1 As Long
  Dim x2 As Long
  Dim x3 As Long
  Dim x4 As Long
  Dim x5 As Long
  Dim bNeedMore As Boolean
  Dim sValue As String
  
  'ASCII Table Reference: http://www.asciitable.com/
  

  'Create the Worksheet object
  Set ws = ThisWorkbook.Sheets("Sheet1")

  
  'Use the System Timer to Generate the first Random Number
  Call Randomize
  
  'Loop until there is a unique Value
  bNeedMore = True
  While bNeedMore = True
  
  
    'Generate a random number between 0 and 25
    'Create the numerical equivalent of an ASCII character between 'a' and 'z'
    x1 = Int(26 * Rnd()) + Asc("a")

    'Generate a random number between 0 and 9
    x2 = Int(10 * Rnd())

    'Generate a random number between 0 and 25
    'Create the numerical equivalent of an ASCII character between 'a' and 'z'
    x3 = Int(26 * Rnd()) + Asc("a")

    'Generate a random number between 0 and 9
    x4 = Int(10 * Rnd())

    'Generate a random number between 0 and 25
    'Create the numerical equivalent of an ASCII character between 'a' and 'z'
    x5 = Int(26 * Rnd()) + Asc("a")

    'Create the entire string
    sValue = Chr(x1) & x2 & Chr(x3) & x4 & Chr(x5)
    Debug.Print sValue
  
  
    'Make sure the value is unique in Column 'A'
    'Find the first occurence of the string in Column 'A'
    Set r = Nothing
    Set r = ws.Columns("A").Find(What:=sValue, _
                        After:=ws.Range("A1"), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
                      
                      
    'Exit if the value is unique
    'Otherwise try again
    If r Is Nothing Then
      bNeedMore = False
    End If
 
  Wend
 
 'Find the last item in Column 'A' (must be row 1 or more)
 'Runtime error is generated if the column is Empty
 On Error Resume Next
 iLastRow = ws.Range("A:A").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 If iLastRow < 1 Then
   iLastRow = 1
 End If
 On Error GoTo 0
 
 'Put the value in Column 'A' in the next row
 ws.Cells(iLastRow + 1, "A").Value = sValue
 
 
 'Clear object pointers
 Set r = Nothing
 Set ws = Nothing
  
End Sub

Lewis

This is a duplicate thread. See http://www.excelforum.com/excel-form...ml#post4132254