Thread: Excel and arrays

Try this:

Option Explicit

Sub RandomCode()
Dim MyCode As String
Dim MyStr As String
Dim chkCnt As Long
Dim chkFIND As Range

StartAgain:
chkCnt = 0
MyCode = UCase(Application.InputBox("Please enter a two letter code", "Your code", "AB", Type:=2))
If Len(MyCode) <> 2 Then
    If MsgBox("Two letter code not entered. Abort?", vbYesNo, "Abort query") = vbYes Then
        Exit Sub
    Else
        GoTo StartAgain
    End If
End If

On Error Resume Next
Do
    MyStr = MyCode & [randbetween(1,100)]
    Set chkFIND = Columns(ActiveCell.Column).Find(MyStr, LookIn:=xlValues, LookAt:=xlWhole)
    If chkFIND Is Nothing Then
        ActiveCell = MyStr
        Exit Sub
    End If
    chkCnt = chkCnt + 1
Loop Until chkCnt > 200

If MsgBox("Code entered failed after 200 random attempts. Try a new code?", vbYesNo, "Try Again") _
    = vbYes Then GoTo StartAgain

End Sub