Generating Random Numbers - Tough One

[Legacy 5232]

Hi Everyone

I need to generate about 2695 random numbers in column A that are 7 digits long, between 1000000:9999999, do not start with zero, and are not duplicates of each other. Does anyone know the formula?

Thanks
Jacob

 

[jindon]

I have updated the code I have already posted above...

 

[Gary Drumm]

Jindon,
It works, thank you.
Can it be stopped at the last active line on the spreadsheet rather than generating a number down to 2695? My requirements were different from the original post in this way:
I would like to have it stop at the last active row of data, but then as soon as a new row of date is entered below, it would generate a new number for that row, etc.
More background; I am tracking Work Orders in a factory. These Work Orders have recently been changed over to an electronic form (whereas the old ones were pre-printed, and had unique numbers already printed on them). The new electronic ones have no number, so I'm trying to find a way to generate a unique number for each new Work Order, and have that number stay with the Work Order as I track it on my spreadsheet, for life. Having no unnecessary numbers after the last Work Order is logged in, is a preference of mine, to keep the spreadsheet cleaner.
I also have a requirement that the random number must stay with the data it is associated with. Once a Work Order is complete, I have a macro that moves that row (in it's entirety) to another sheet named "Complete". I plan to do some Autofilter tracking exercises with the data from the "Complete" sheet.
Sorry to be so long winded.
Thanks
Gary

 

[jindon]

OK

Sub addUniqueNum()
Dim a, e As Variant, x As Long
a = Range("a1",Range("a" & Rows.Count).End(xlUp)).Value
With CreateObject("Scripting.Dictionary")
    For Each e In a
        If Not .Exists(e) Then .Add e, Nothing
    Next
    Erase a
    Randomize
    Do
Again:
         x = Int(Rnd * 900000) + 100000
         If .Exists(x) Then GoTo Again
    Loop Until Not .Exists(x)
End With
Range("a" & Rows.Count).End(xlUp).Offset(1).VAlue = x
End Sub

 

[Gary Drumm]

Jindon,
When running, it immediately says Type Mismatch 13.
Have I done something wrong?
I have column A cells formatted as "Numbers", with no decimals.
Thanks

 

[Gary Drumm]

Sub addUniqueNum(()
I'm getting RED text here on the first line, are these parenthesis correct?
I think the type mismatch may have been associated with that?
But I really don't know for sure.

Many thanks again, I appreciate your patience

 

[jindon]

OOps

I have added two lines in the previous code.

Do you want to try it again?

 

[Gary Drumm]

Yes please,
Thank you

 

[jindon]

Sub addUniqueNum(()
I'm getting RED text here on the first line, are these parenthesis correct?
I think the type mismatch may have been associated with that?
But I really don't know for sure.

Many thanks again, I appreciate your patience

Yep! and I have corrected it.

 

[Gary Drumm]

Yes please,
Thank you

 

[jindon]

OK

Sub addUniqueNum()
Dim a, e As Variant, x As Long
a = Range("a1",Range("a" & Rows.Count).End(xlUp)).Value
With CreateObject("Scripting.Dictionary")
    For Each e In a
        If Not .Exists(e) Then .Add e, Nothing
    Next
    Erase a
    Randomize
    Do
Again:
         x = Int(Rnd * 900000) + 100000
         If .Exists(x) Then GoTo Again
    Loop Until Not .Exists(x)
End With
Range("a" & Rows.Count).End(xlUp).Offset(1).VAlue = x
End Sub