Random Numbers Greater Than 65,000

[PAB]

Hi Everyone,

I Use the Following Code to Generate Random Numbers :-

Sub Sets()
Application.ScreenUpdating = False
Dim my(1 To 49)

For j = 1 To 10
' Reinitialize Array Before Selecting.
For i = 1 To 49
my(i) = i
Next i

For k = 1 To 6
Randomize
NewNumber:
Number = Int(49 * Rnd) + 1
If my(Number) = "" Then
GoTo NewNumber
Else
Cells(j, k) = my(Number)
my(Number) = ""
End If
Next k
Next j
Application.ScreenUpdating = True
End Sub

This Works Excellent and Does NOT give a Repeated Number ( 1 to 49 ) in Any Set of Six Numbers.
I would like to be able to Produce a Random Number List of 250,000 + Sets of Six Numbers.
How can the Above Code be Modified so that when it Reaches 65,000 Sets of Six Numbers it Moves Eight Columns to the Right and goes Back to Row One. I know it needs something like :-

ActiveCell.Offset(-65000, 8).Select

I do NOT know where this should be Entered into the Above Code OR the Extra Coding that is Needed in Order for this to Work.
Any Help will be Appreciated.

All the Best
PAB

 

[GillesD]

250,000 random sets

This should do what you want:

1 - Add the following sub:
Sub Run_Sets()
For nCount = 1 To 4
Call Sets
ActiveCell.Offset(0, 8).Select
Next nCount
End Sub

2 - Change the line "For j = 1 To 10" to "For j = 1 To 62500"

3 - Change the line "Cells(j, k) = my(Number)" to "ActiveCell.Offset(j - 1, k - 1).Value = my(Number)"

4 - Run the macro Run_Sets and you should get 4 sets of 62,500 combinations with different random numbers.

5 - Adjust the nCount and/or the j variables to get a different number of sets; the current macro gives 250,000 sets.

 

[KIM]

WOW that is amazing !

It actually works.

 

[PAB]

Thanks GillesD, it does Exactly what I want.

All the Best
PAB

 

[thornc]

WOW that is amazing !
It actually works.

What were you expecting, we have very knowledged members here. Gilles is a pro when it comes to Excell!

 

[GillesD]

Randon number combination

PAB, I already had a macro that did what you wanted but not up to 250,000 combinations. It took a simple adjustement to get it up to that level (or any number really).

The only difference was that my macro sorts the 6 numbers in ascending numbers on a line.