Unique Random Between

theboyscout

New Member
Joined
Nov 29, 2012
Messages
26
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,091
Office Version
2007
Platform
Windows
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


Code:
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
 
Last edited:

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Why bother with random numbers for this kind of thing ?
Why not allocate numbers sequentially from the range ?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,372
Messages
5,444,074
Members
405,265
Latest member
Iram

This Week's Hot Topics

Top