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

 

[Gary Drumm]

Yet one more error popping up

Run-time error 438

Object doesn't support this property or method

If you still have the patience, I will be most grateful.

 

[jindon]

typo!!!

at the end of 3rd line

Vaue

Should read as Value
sorry for that.

 

[jindon]

Gary

I'm afraid if I misread your requirements....

Which column is to be generated to the last row of which column?

say if you have dates in Col. A , is Col.B should be generated?

I only got 20 Min to go for today..

 

[jindon]

If my assumption above is correct then

Sub Sample
Dim x As Long, y As Long
y = Range("a" & Rows.Count).End(xlUp).Row
Randomize
With CreateObject("Scripting.Dictionary")
    Do
Again:
        x = Int(Rnd * 9000000) + 100000
        If .exists(x) Then GoTo Again
        .add x, Nothing
    Loop While .Count <=y -1
Range("b2").Resize(.Count) = Application.Transpose(.keys)
End With
End Sub

Then this goes to sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
With Target
       If .Count>1 Then Exit Sub
       If .Column <> 1 Then Exit Sub
       If IsEmpty(.Value) Then Exit Sub
End With
Again:
x = Int(Rnd * 900000) + 100000
If Application.CountIf(Range("a:a"),x))>0 Then GoTo Again
Application.EnableEvents = False
Target.Offset(, 1).VAlue = x
Application.EnableEvents = True
End Sub

 

[Gary Drumm]

Jindon,
It's back to run-time error
Type mismatch 13.

I've got to get some sleep, if you care to reply, I will gratefully follow up tomorrow.

Thank you so much for your help.
It's 2:39 am here in Dallas, Texas USA

 

[tusharm]

Use the code in
Return a specified number of random numbers from a set of numbers with a specified lower and upper values
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/vba.html#from_bounds

While it is designed to be called from another VB routine, it can be adapted as a UDF. Suppose you want 15 numbers in one column. Select the range of 15 cells, and array-enter =TRANSPOSE(TMOptRands(1000000,9999999,15))

Next, copy those cells and Edit | Paste Special... | Values.

Tushar,
I'm afraid these examples won't work for what I need.
Or perhaps they will, but I just can't see how.
It appears they generate random numbers from a pre-existing set of numbers. What I need is a way to create unique 7 digit numbers from nothing, and have them permenantly assigned to the data that follows on the same row that they occupy. I also need the other requirements I listed in my post, in order to add the flexability I need.
Thanks for the link anyway.
Gary

 

[Gary Drumm]

Tusharm, Jindon, thank you

I am such a rookie that I can't interpret what you are saying. I've looked at your site, and it totally confuses me. Perhaps when I have a little more time to concentrate on it I will try again.

Thanks to you and Jindon for your help.

Jindon, My first column is reserved for the unique number I've been revering to.

I've had problems trying to post a sample, getting error codes which I have emailed Colo about.

Thanks again.
Gary Drumm

 

[jindon]

Not sure which part generating error though,,
Now Sample subroutine is to generate unique numbers in Col.A to the end of Last row of col.B
Note: Use only when you need to. If you already have them, you don't need this..

Worksheet module code is to generate New Unique Number for New entry in Col.B

Sub Sample
Dim x As Long, y As Long
y = Range("b" & Rows.Count).End(xlUp).Row
If y < 2 Then Exit Sub
Randomize
With CreateObject("Scripting.Dictionary")
    Do
Again:
        x = Int(Rnd * 9000000) + 100000
        If .exists(x) Then GoTo Again
        .add x, Nothing
    Loop While .Count <=y -1
Range("b2").Resize(.Count) = Application.Transpose(.keys)
End With
End Sub

Then this goes to sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
With Target
       If .Count>1 Then Exit Sub
       If .Column <> 2 Then Exit Sub
       If Not IsEmpty(.Offset(,-1)) Then Exit Sub
       If IsEmpty(.Value) Then Exit Sub
End With
Again:
x = Int(Rnd * 900000) + 100000
If Application.CountIf(Range("a:a"),x))>0 Then GoTo Again
Application.EnableEvents = False
Target.Offset(, 1).Value = x
Application.EnableEvents = True
End Sub

 

[Gary Drumm]

Jindon,
If Application.CountIf(Range("a:a"),x))>0 Then GoTo Again
This line is showing up in red, I've looked at Help, but I don't understand it at all. Something about it expecting a call? I've tried a few things to no avail.
Whicj one gets copies to the Sheet 1, and should I create a module to copy the other one too?
Thanks

 

[jindon]

Sorry too much ")"

should be

If Application.CountIf(Range("a:a"),x)>0 Then