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
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.
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
442
Peter_SSs
Peter_SSs
M
  • Question
Multiplying numbers separated by commas in multiple cells
Replies
8
Views
151
mikeloz11
M
P
  • Solved
How to copy specific columns to other sheet in office 2021 without vba with some filter and format..
Replies
7
Views
141
Fluff
Fluff
H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
321
Peter_SSs
Peter_SSs
A
  • Question
Matching a number sequence within a group of numbers in a cell
Replies
1
Views
295
FormR
FormR

Forum statistics

Threads
1,216,106
Messages
6,128,863
Members
449,473
Latest member
soumyahalder4

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