Pickup Data from a Worksheet
Good afternoon,
I found this while doing a search. It works fine, but how can I adapt it to take the number of balls drawn which is in cell H3, the number of balls drawn from which is in cell I3 and the number of combinations I want to randomly produce which is in cell J3. These three cells are in a worksheet called ‘Random Numbers’. Sub lottery() Dim my(1 To 49) For j = 1 To 10 ' Reinit 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 End Sub Thanks in advance, Paul |
Pickup Data from a Worksheet
On Oct 18, 3:00*pm, Paul Black wrote:
Good afternoon, I found this while doing a search. It works fine, but how can I adapt it to take the number of balls drawn which is in cell H3, the number of balls drawn from which is in cell I3 and the number of combinations I want to randomly produce which is in cell J3. These three cells are in a worksheet called ‘Random Numbers’. Sub lottery() *Dim my(1 To 49) *For j = 1 To 10 *' Reinit 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 End Sub Thanks in advance, Paul Has anyone got any ideas please? |
Pickup Data from a Worksheet
On Oct 19, 1:45*pm, Paul Black wrote:
On Oct 18, 3:00*pm, Paul Black wrote: Good afternoon, I found this while doing a search. It works fine, but how can I adapt it to take the number of balls drawn which is in cell H3, the number of balls drawn from which is in cell I3 and the number of combinations I want to randomly produce which is in cell J3. These three cells are in a worksheet called ‘Random Numbers’. Sub lottery() *Dim my(1 To 49) *For j = 1 To 10 *' Reinit 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 End Sub Thanks in advance, Paul Has anyone got any ideas please?- Hide quoted text - - Show quoted text - I have tried something like this but to no avail ... Sub Random_Lotto_Numbers() Dim nDrawn As Long Dim nfrom As Long Dim nComb As Long Dim number As Variant Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayAlerts = False Worksheets("Random Lotto Numbers").Select nDrawn = Range("H3").Value nfrom = Range("I3").Value nComb = Range("J3").Value For j = 1 To nComb ' Number Of Combinations ' Reinitialize Array Before Selecting New Line For I = 1 To nfrom my(I) = I Next I For k = 1 To nDrawn ' Pick 6 Numbers Per Combination Randomize NewNumber: number = Int(nfrom * Rnd) + 1 If my(number) = "" Then GoTo NewNumber Else Cells(j, k) = my(number) my(number) = "" End If Next k Next j Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub .... kind regards, Paul |
Pickup Data from a Worksheet
On Oct 19, 3:38*pm, Paul Black wrote:
On Oct 19, 1:45*pm, Paul Black wrote: On Oct 18, 3:00*pm, Paul Black wrote: Good afternoon, I found this while doing a search. It works fine, but how can I adapt it to take the number of balls drawn which is in cell H3, the number of balls drawn from which is in cell I3 and the number of combinations I want to randomly produce which is in cell J3. These three cells are in a worksheet called ‘Random Numbers’. Sub lottery() *Dim my(1 To 49) *For j = 1 To 10 *' Reinit 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 End Sub Thanks in advance, Paul Has anyone got any ideas please?- Hide quoted text - - Show quoted text - I have tried something like this but to no avail ... Sub Random_Lotto_Numbers() Dim nDrawn As Long Dim nfrom As Long Dim nComb As Long Dim number As Variant Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayAlerts = False Worksheets("Random Lotto Numbers").Select nDrawn = Range("H3").Value nfrom = Range("I3").Value nComb = Range("J3").Value For j = 1 To nComb * *' * Number Of Combinations ' * Reinitialize Array Before Selecting New Line * * For I = 1 To nfrom * * * * my(I) = I * * Next I * * For k = 1 To nDrawn *' * Pick 6 Numbers Per Combination * * * * Randomize NewNumber: * * * * number = Int(nfrom * Rnd) + 1 * * * * If my(number) = "" Then * * * * * * GoTo NewNumber * * * * Else * * * * * * Cells(j, k) = my(number) * * * * * * my(number) = "" * * * * End If * * Next k Next j Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub ... kind regards, Paul- Hide quoted text - - Show quoted text - Good afternoon, I have come up with something that works except for one thing. How can I change ... Dim my(1 To 49) .... so it says something like 1 to 'nFrom' instead of having a fixed number e.g. 49? I don't want to have to change the actual program every time I want to use it? Also to be honest I am unsure what my(Number) actually is doing. Anyway, here is the full code ... Sub Random_Lotto_Numbers() Dim nDrawn As Long Dim nFrom As Long Dim nComb As Long Dim my(1 To 49) ' < Line causing the problem Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayAlerts = False Worksheets("Random Lotto Numbers").Select With ActiveSheet Range("A1:F65500").Select Selection.ClearContents nDrawn = .Range("H3").Value nFrom = .Range("I3").Value nComb = .Range("J3").Value End With For j = 1 To nComb ' Number of combinations ' Reinitialize Array Before Selecting New Line For I = 1 To nFrom ' Total numbers to be drawn from my(I) = I Next I For k = 1 To nDrawn ' Numbers in each combination Randomize NewNumber: Number = Int(nFrom * Rnd) + 1 If my(Number) = "" Then GoTo NewNumber Else Cells(j, k) = my(Number) my(Number) = "" End If Next k Next j Range("H9").Select Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Thanks, Paul |
All times are GMT +1. The time now is 04:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com