Unique Random Between

[theboyscout]

I'm creating a unique number for new users of a list of 3000. What I would like to do is create random numbers which do not already exist in the list of prior users.

Something like:
randombetween(100000, 999999) <> A3

Thanks in advance

 

[DanteAmor]

Change A3 to A4 by the range of cells where you already have your numbers. Change 30 to the number of random numbers you want. The result will be in column B

Sub Unique_Random_Between()
'
    Dim num As New Collection
    On Error Resume Next
    n = [COLOR=#0000ff]30[/COLOR]      'change to 3000
    Set rango = Range("[COLOR=#0000ff]A3:A4[/COLOR]")  'numbers already exist
    For Each celda In rango
        valor = celda.Value
        num.Add Item:=valor, Key:=CStr(valor)
    Next
    Do While num.Count < n
        valor = WorksheetFunction.RandBetween(100000, 999999)
        num.Add Item:=valor, Key:=CStr(valor)
         
    Loop
    For i = 1 To num.Count
        Cells(i, "B").Value = num(i)
    Next
    MsgBox "End"
End Sub

 

[Gerald Higgins]

Why bother with random numbers for this kind of thing ?
Why not allocate numbers sequentially from the range ?