Produce Random Numbers

[Paul Black]

Good evening,

I am trying to write a program that outputs 5 random numbers without
repetition from 50 numbers.
The number of combinations to be produced is in worksheet "Random
Numbers" and in cell "P3".
The code below works as far as the above is concerned.

Sub Random_Numbers_Generator()
Dim nDrawnMain As Long
Dim nFromMain As Long
Dim nDrawnLucky As Long
Dim nFromLucky As Long
Dim nComb As Long
Dim myMain() As Variant
Dim myLucky() As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

nDrawnMain = 5
nFromMain = 50
nDrawnLucky = 2
nFromLucky = 9

Worksheets("Random Numbers").Select

With ActiveSheet
Range("A1:J65536").Select
Selection.ClearContents
ReDim myMain(1 To nFromMain)
ReDim myLucky(1 To nFromLucky)
' nDrawn = .Range("N3").Value
' nFrom = .Range("O3").Value
nComb = .Range("P3").Value
End With

For j = 1 To nComb

For H = 1 To nFromMain
myMain(H) = H
Next H

For k = 1 To nDrawnMain
Randomize
NewNumber:
Number = Int(nFromMain * Rnd) + 1
If myMain(Number) = "" Then
GoTo NewNumber
Else
Cells(j, k) = myMain(Number)
myMain(Number) = ""
End If
Next k
Next j

Range("N3").Select

Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

What I would like to add is to output 2 random numbers without
repetition from 9 numbers.
I would like to output these 2 extra numbers after it has produced the
5th number and skipped two cells to the right from the previous
combination.
Both these combinations are totally seperate from each other so could
possibly have the same numbers in them.

So basically, the program will produce two sets of random numbers
without repetition:-
The first being 5 numbers from 50 numbers.
The second being 2 numbers from 9 numbers.
The 5 numbers will be output in cells A1:E1 and the 2 numbers will be
output in cells G1:H1.

Thanks in advance,
Paul

 

[joeu2004]

So basically, the program will produce two sets of
random numbers without repetition:-
The first being 5 numbers from 50 numbers.
The second being 2 numbers from 9 numbers.
The 5 numbers will be output in cells A1:E1 and the
2 numbers will be output in cells G1:H1.

Try the following....

-----

Randomize

For j = 1 To nComb

For h = 1 To nFromMain
myMain(h) = h
Next h

n = nFromMain
For k = 1 To nDrawnMain
h = Int(n * Rnd) + 1
Range("a1").Offset(j - 1, k - 1) = myMain(h)
If h <> n Then myMain(h) = myMain(n)
n = n - 1
Next k

For h = 1 To nFromLucky
myLucky(h) = h
Next

n = nFromLucky
For k = 1 To nDrawsLucky
h = Int(n * Rnd) + 1
Range("a1").Offset(j - 1, nDrawnMain + k) = myLucky(h)
If h <> n Then myLucky(h) = myLucky(n)
n = n - 1
Next

Next j

-----

One comment....

You might prefer to use Evaluate("RAND()") instead of Rnd. Excel RAND has
better random characteristic than VBA Rnd, especially starting in XL2010.

Evaluate("RAND()") is much slower to execute from VBA. But that might not
be noticable is you generating only 100 or fewer combinations.

If you choose to use Evaluate("RAND()"), the Randomize statement becomes
useless.

PS: If you use Rnd, the Randomize statement only needs to be done once.

 

[Paul Black]

Try the following....

-----

Randomize

For j = 1 To nComb

    For h = 1 To nFromMain
        myMain(h) = h
    Next h

    n = nFromMain
    For k = 1 To nDrawnMain
       h = Int(n * Rnd) + 1
       Range("a1").Offset(j - 1, k - 1) = myMain(h)
       If h <> n Then myMain(h) = myMain(n)
       n = n - 1
    Next k

    For h = 1 To nFromLucky
       myLucky(h) = h
    Next

    n = nFromLucky
    For k = 1 To nDrawsLucky
       h = Int(n * Rnd) + 1
       Range("a1").Offset(j - 1, nDrawnMain + k) = myLucky(h)
       If h <> n Then myLucky(h) = myLucky(n)
       n = n - 1
    Next

Next j

-----

One comment....

You might prefer to use Evaluate("RAND()") instead of Rnd.  Excel RAND has
better random characteristic than VBA Rnd, especially starting in XL2010.

Evaluate("RAND()") is much slower to execute from VBA.  But that might not
be noticable is you generating only 100 or fewer combinations.

If you choose to use Evaluate("RAND()"), the Randomize statement becomes
useless.

PS:  If you use Rnd, the Randomize statement only needs to be done once..

Thanks joeu2004,

Thats exactly what I was after.
I am using Excel 2007 but will have a look at Evaluate("RAND()") as
you suggest and try to adapt the code to see if it makes any
difference.
I tried to run the program using only say 5 numbers from 50 and
exclude producing the second set of numbers and it gave me a ...

Run-time error '9'
Subscript out of range

.... I managed to get around this by commenting out ...

ReDim myLucky(1 To nFromLucky)

Thanks again, it is much appreciated.

Kind regards,
Paul

 

[joeu2004]

I tried to run the program using only say 5 numbers
from 50 and exclude producing the second set of numbers
and it gave me a ...
Run-time error '9'
Subscript out of range
... I managed to get around this by commenting out ...
ReDim myLucky(1 To nFromLucky)

Off-hand, I cannot see any reason why the change would lead to that error or
why the work-around would correct it. I suspect that there is still a
defect in your code, and it really is not working as you intend it to. You
have just gotten lucky because of the macro's random nature.

If you can reproduce the "out of range" error, I suggest that you post
__that__ macro (the one that fails) so that we can help you correct the root
cause.

 

[Paul Black]

Off-hand, I cannot see any reason why the change would lead to that erroror
why the work-around would correct it.  I suspect that there is still a
defect in your code, and it really is not working as you intend it to.  You
have just gotten lucky because of the macro's random nature.

If you can reproduce the "out of range" error, I suggest that you post
__that__ macro (the one that fails) so that we can help you correct the root
cause.

Hi joeu2004,

Thanks for the reply.
I would have answered earlier but the discussion group has only just
been updated.
Anyway, here is the code that produces the error ...

Sub Random()

Dim nDrawnMain As Long
Dim nFromMain As Long
Dim nDrawnLucky As Long
Dim nFromLucky As Long
Dim nComb As Long
Dim myMain() As Variant
Dim myLucky() As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

nDrawnMain = 5
nFromMain = 50
nDrawnLucky = 0
nFromLucky = 0

Worksheets("Random Numbers").Select

With ActiveSheet
.Columns("A:K").ClearContents
ReDim myMain(1 To nFromMain)
ReDim myLucky(1 To nFromLucky)
nComb = .Range("N18").Value
End With

Randomize

For j = 1 To nComb

For h = 1 To nFromMain
myMain(h) = h
Next h

n = nFromMain
For k = 1 To nDrawnMain
h = Int(n * Rnd) + 1
Range("B2").Offset(j - 1, k - 1) = myMain(h)
If h <> n Then myMain(h) = myMain(n)
n = n - 1
Next k

For h = 1 To nFromLucky
myLucky(h) = h
Next

n = nFromLucky
For k = 1 To nDrawnLucky
h = Int(n * Rnd) + 1
Range("B2").Offset(j - 1, nDrawnMain + k) = myLucky(h)
If h <> n Then myLucky(h) = myLucky(n)
n = n - 1
Next

Next j

Range("O18").Select

Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Thanks.

Kind regards,
Paul

 

[joeu2004]

Anyway, here is the code that produces the error [....]
nDrawnLucky = 0
nFromLucky = 0

Well, of course. In your originally posting, you wrote:

nDrawnLucky = 2
nFromLucky = 9

With that plus a positive (non-zero) value in N18, it seems to work just
fine.

 

[Walter Briscoe]

In message <[email protected]
s.com> of Wed, 10 Aug 2011 08:01:53 in microsoft.public.excel.programmin

The problem with ReDim myLucky(1 To nFromLucky) is that nFromLucky is 0.
The upper bound of an array can't be less than its lower bound.

Hi joeu2004,

Thanks for the reply.
I would have answered earlier but the discussion group has only just
been updated.

I suspect you are using Google to access usenet.
If so, do yourself a favour and get a real news reader and leave Google
to what it does well. <http://en.wikipedia.org/wiki/Newsreader_(Usenet
%29> may help. I use the following to read and write news - both have
never simultaneously failed for me - free.teranews.com and
news.eternal-september.org.

Anyway, here is the code that produces the error ...

Option Explicit ' is a good thing!

Sub Random()

Dim nDrawnMain As Long

I infer you don't know about Const nDrawnMain As Long = 5

Dim nFromMain As Long
Dim nDrawnLucky As Long
Dim nFromLucky As Long
Dim nComb As Long
Dim myMain() As Variant
Dim myLucky() As Variant

Dim j As Long, h As Long, n As Long, k As Long ' Needed as _
Option Explicit used above.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

I would comment those 3 lines until you have software which does as you
intend. I see no reason for Application.DisplayAlerts = False. I suspect
you have copied and pasted it because another author used it. If you
click on DisplayAlerts in that line and press F1, Excel help should
allow you to understand that line, which is not needed in the code you
post.

nDrawnMain = 5
nFromMain = 50
nDrawnLucky = 0
nFromLucky = 0

nFromLucky = 5 ' >= 1 for ReDim myLucky(1 To nFromLucky) below to work

Worksheets("Random Numbers").Select

With ActiveSheet
.Columns("A:K").ClearContents
ReDim myMain(1 To nFromMain)
ReDim myLucky(1 To nFromLucky)
nComb = .Range("N18").Value

Debug.Assert nComb >= 1 ' Quick and dirty plausibility check.

End With

Randomize

For j = 1 To nComb

For h = 1 To nFromMain
myMain(h) = h
Next h

n = nFromMain
For k = 1 To nDrawnMain
h = Int(n * Rnd) + 1
Range("B2").Offset(j - 1, k - 1) = myMain(h)
If h <> n Then myMain(h) = myMain(n)
n = n - 1
Next k

For h = 1 To nFromLucky
myLucky(h) = h
Next

n = nFromLucky
For k = 1 To nDrawnLucky
h = Int(n * Rnd) + 1
Range("B2").Offset(j - 1, nDrawnMain + k) = myLucky(h)
If h <> n Then myLucky(h) = myLucky(n)
n = n - 1
Next

Next j

Range("O18").Select

Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Thanks.

Kind regards,
Paul

I suggest you step through your code in the debugger.
To get to that in Excel 2003, I do Alt+F8 (or Tools/Macro/Macros...),
select Random and click Step Into. You need simple instructions on
stepping through the code. I hope Google will find them for you.

I renamed "Sheet1" as "Random Numbers" and put 1 in N18.
I stepped through and found numbers were written to "B2:F2".
I infer you expect something to be written to "O18", but do not feel
like finding out what line you expect to do so - none do and I see none
likely to do so.

You have not said which version of Excel you use. The controls are quite
different in Excel 2007 and Excel 2010 and, probably, in Excel 2011 for
Mac.

After I started writing this, (e-mail address removed) suggested you originally
had nDrawnLucky = 2: nFromLucky = 9 and that with those restored, the
code works fine. It still does not write "O18", but does not crash.

HTH