Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Produce Random Numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Produce Random Numbers
"Paul Black" wrote:
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Produce Random Numbers
On Jul 31, 1:18*am, "joeu2004" wrote:
"Paul Black" wrote: 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.. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Produce Random Numbers
"Paul Black" wrote:
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Produce Random Numbers
On Aug 3, 5:55*pm, "joeu2004" wrote:
"Paul Black" wrote: 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. 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Produce Random Numbers
"Paul Black" wrote:
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Produce Random Numbers
In message
s.com of Wed, 10 Aug 2011 08:01:53 in microsoft.public.excel.programmin g, Paul Black writes On Aug 3, 5:55*pm, "joeu2004" wrote: "Paul Black" wrote: 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) 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. 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. 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_%28Usenet %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, 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 -- Walter Briscoe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I produce consecutive invoice numbers in Excel? | Excel Worksheet Functions | |||
How do I produce Automatic Quotation Numbers in Excel? | Excel Discussion (Misc queries) | |||
Delete Numbers and Produce a List | Excel Programming | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) |