How to Generate 5/59 lotto numbers in 1 cell

T

testing lotto

New Member
Joined
Aug 7, 2011
Messages
29
I am looking for a way to generate 5 random lottery numbers sorted in numerical order in one cell, not 5 cells. for example, 03-13-22-39-51 contained in cell A1. Not 03 in cell A1 and 13 in B1, 22 in C1, 39 in D1, 51 in E1. I think it should be possible to do this ?
 
Sort by date Sort by votes

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
testing lotto said:
Thanks for all the effort to help, but it runs for a few seconds then locks up excel ? seems to get stuck in an endless loop.
Click to expand...
It doesn't lock up excel and get stuck in an endless loop. At least not for me it doesn't.

It just calculates Lotto combinations in the background (1million per row) and compares them with the historical Lotto results which you say are listed in columnA. Isn't that what you wanted??

If no match after a million calculations, it goes on to the next row.

But as it stands, that code does need some data in ColumnA.

Change the mx=10^6 at the top to mx=10 and see if it still appears to lock up Excel.
 
Upvote 0
This is what I get.

<TABLE style="WIDTH: 318pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=424><COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=125>Lotto History</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=132>Generated #'s</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 125pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" width=167 colSpan=2>Number of tries till A2=B2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>13-19-21-28-49</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>20-40-41-47-55</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>38-40-41-51-59</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>01-07-27-38-48</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>01-04-38-40-42</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>24-28-48-50-54</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>08-18-19-32-54</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>10000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>01-09-11-23-31</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>11-15-24-50-55</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>01-11-18-29-51</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>24-30-45-57-59</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>18-36-39-41-57</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>12-15-19-46-59</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>12-21-22-38-41</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>19-20-38-41-43</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=125>16-18-27-36-50</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>
 
Upvote 0
Option Explicit
Const u = 5 '5 numbers per game
Const v = 59 'Random numbers from 1 to 59
Const num = 20 '?
Const mx = 10 ^ 4 '1 million tries then end
Sub lottonumbers2() 'SubRouteen Name
Dim b() As Boolean '
Dim c(), d As Object, a, w As String
Dim i&, j&, k&, x&, q&, nr& ' the 5 random numbers + # of tries as string
nr = Range("A1").End(xlDown).Row
a = Range("A1:A" & nr)
Range("B2:C" & nr).ClearContents
Application.ScreenUpdating = True
Randomize 'Random seed for Rnd
For j = 2 To nr
Application.ScreenUpdating = True
For q = 1 To mx
ReDim b(v), c(1 To u)
k = 0
Do
x = Int(Rnd * v) + 1
If b(x) = False Then
k = k + 1
b(x) = True
End If
Loop Until k = 5
k = 0
For i = 1 To v
If b(i) Then k = k + 1: c(k) = i
Next i
w = Join(c, "-")
If w = a(j, 1) Then
Cells(j, 2) = w
Cells(j, 3) = q
Exit For
End If
Next q
If Cells(j, 3) = "" Then Cells(j, 3) = mx
Next j
End Sub



I made mx lower just to see how it runs without locking up. I should see the numbers being generated in column B changing and the count in C going up as it is running. I do realize this will slow it way down. Esc and Ctrl+Break don't work when it locks up, is there another way to stop besides Ctrl-Alt-Del and end task to stop it when it gets stuck ?
 
Upvote 0
testing lotto said:
I made mx lower just to see how it runs without locking up. I should see the numbers being generated in column B changing and the count in C going up as it is running. I do realize this will slow it way down. Esc and Ctrl+Break don't work when it locks up, is there another way to stop besides Ctrl-Alt-Del and end task to stop it when it gets stuck ?
Click to expand...
I didn't realize that seeing the changing display of numbers was that important to you. And yes, with your probabilities of matching in this situation being so low, it would slow it way, way, WAY, down.

Here's the code I last posted, with several modifications so as to display what you seem to want.

Run it and see ...
Rich (BB code): 
Option Explicit
Const u = 5
Const v = 59
Const mx = 10 ^ 3 'changed, to illustrate code doesn't lock up
Sub lottonumbers2() 'as posted but with 4 modifications
Dim b() As Boolean
Dim c(), d As Object, a, w As String
Dim i&, j&, k&, x&, q&, nr&
nr = Range("A" & Rows.Count).End(xlUp).Row 'changed, to better define historical range
a = Range("A1:A" & nr)
Range("B2:C" & nr).ClearContents
Randomize
For j = 2 To nr
    For q = 1 To mx
    ReDim b(v), c(1 To u)
    k = 0
    Do
        x = Int(Rnd * v) + 1
        If b(x) = False Then
            k = k + 1
            b(x) = True
        End If
    Loop Until k = 5
    k = 0
    For i = 1 To v
        If b(i) Then k = k + 1: c(k) = i
    Next i
    w = Join(c, "-")
    Cells(j, 2) = w 'added, for display purpose only
    Cells(j, 3) = q 'added, for display purpose only
    If w = a(j, 1) Then
        Cells(j, 2) = w
        Cells(j, 3) = q
        Exit For
    End If
    Next q
    If Cells(j, 3) = "" Then Cells(j, 3) = mx
Next j
End Sub
 
Upvote 0
I have 2 questions.

1. Will the leading 0's on single digit numbers affect the compareing of column a and b ? ex. (does 01=1 or not).

2. It still locks up on me after a few seconds when changing mx to a larger number, and Esc & Ctrl+Break dont work? I have to use Alt Ctrl Del - End Task. Then start excel again. I tried it on 2 different computers, both have excel 10, 1 has Vista the other has windows 7. Is there another way to stop the program when it gets stuck?
 
Upvote 0
testing lotto said:
I have 2 questions.

1. Will the leading 0's on single digit numbers affect the compareing of column a and b ? ex. (does 01=1 or not).

2. It still locks up on me after a few seconds when changing mx to a larger number, and Esc & Ctrl+Break dont work? I have to use Alt Ctrl Del - End Task. Then start excel again. I tried it on 2 different computers, both have excel 10, 1 has Vista the other has windows 7. Is there another way to stop the program when it gets stuck?
Click to expand...
Regarding 1., yes the leading zero on single digit numbers will make a difference when comparing those sorts of strings.

I don't know much about Lotto and didn't think of that when generating the Lotto strings, so I generated them wrongly.

Easily remedied though. Code modified as indicated below will include the relevant leading zeros.


Regarding 2., you've been nonspecific throughout about just what you mean by the code "locking up". I've run that code several times and every time it's run through to the end without problems. Took me about 10 minutes to compare 27 historical numbers with mx=10^7.
What exactly do you mean by "locking up"? Does it run OK with mx=1, mx=100, mx=100,000, mx=10^7 etc? Or does it not work with any value of mx?, or any value greater than ???
Rich (BB code): 
Option Explicit
Const u = 5
Const v = 59
Const mx = 3 'changed, to illustrate code doesn't lock up
Sub lottonumbers2() 'reposted with modification
Dim b() As Boolean, t
Dim c(), d As Object, a, w As String
Dim i&, j&, k&, x&, q&, nr&
t = Timer
nr = Range("A" & Rows.Count).End(xlUp).Row 'changed, to better define historical range
a = Range("A1:A" & nr)
Range("B2:C" & nr).ClearContents
Randomize
For j = 2 To nr
    For q = 1 To mx
    ReDim b(v), c(1 To u)
    k = 0
    Do
        x = Int(Rnd * v) + 1
        If b(x) = False Then
            k = k + 1
            b(x) = True
        End If
    Loop Until k = 5
    k = 0
    For i = 1 To v
        If b(i) Then k = k + 1: c(k) = i: _
            If i < 10 Then c(k) = "0" & c(k)
    Next i
    w = Join(c, "-")
    'Cells(j, 2) = w 'added, for display purpose only
    'Cells(j, 3) = q 'added, for display purpose only
    If w = a(j, 1) Then
        Cells(j, 2) = w
        Cells(j, 3) = q
        Exit For
    End If
    Next q
    If Cells(j, 3) = "" Then Cells(j, 3) = mx
Next j
MsgBox Timer - t
End Sub
 
Upvote 0
mx=100,000, will do 5 maybe 6 rows then the hour glass spins (the lights are on but nobody is home) the only way to get out of the program is "End Tast" mx= 100,000,000= don't work at all?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Can I generate a random order but with two inputted numbers?
Replies
5
Views
711
Peter_SSs
Peter_SSs
PeteWright
  • Solved
Pad number (that comes from a formula) with zeros (before and after)
Replies
5
Views
362
hagia_sofia
hagia_sofia
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
274
shift-del
shift-del
Dan Wilson
  • Solved
Formula help.
2
Replies
10
Views
221
Jeffrey Mahoney
Jeffrey Mahoney
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
271
tezza
tezza

Forum statistics

Threads
1,223,958
Messages
6,175,628
Members
452,661
Latest member
Nonhle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top