all lottery combinations 1 - 49

damainman

New Member
Joined
Feb 10, 2008
Messages
16
i need excel to output all lottery combinations. the numbers are from 1-49 from which 6 numbers are drawn. i do realise the number of combinations are 13983816. i want the 6 numbers drawn to be in different cells across 6 different columns. so the 1st 6 numbers are in a1, b1, c1, d1, e1, f1. obviously excel 2003 has 65536 rows and so the formula/vb code should carry on onto a new worksheet or ideally a new workbook. its ideal in a new workbook so not to end up with a single huge 10gig file, but on several worksheets in 1 workbook is fine.

i have been trying to do this myself for a while now with no success. any help would be great. from what i understand; this is a bit of a challenge i know.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try this:

Code:
Sub allLottery()
    Dim x As Long
    Application.ScreenUpdating = False
    x = 1
    x = doTheLott("", x, 1)
End Sub
Function doTheLott(ByVal xStr As String, r As Long, a As Integer) As Long
    Dim xArr As Variant
    xArr = Split(Trim(xStr), " ")
    If UBound(xArr) = 5 Then
        Cells(r, 1).Resize(1, 6).Value = xArr
        r = r + 1
        If r > Rows.Count Then
            Sheets.Add
            r = 1
        End If
    Else
        For i = a To 49
            j = Format(i, "00")
            If InStr(xStr, j) = 0 Then
                r = doTheLott(xStr & j & " ", r, a + 1)
            End If
        Next
    End If
    doTheLott = r
End Function
Run allLottery and then go make a coffee. No idea how long it'll take (or even if it actually works...)
 
Upvote 0
its just for personal non work related purposes. i want to be able to then play about with the combinations and query/analyse the list.

it would be great if someone could give me some help.
 
Upvote 0
thanks for that solution....
...i am literrally having to have a tea! lol

i shall let you know what happens!
 
Upvote 0
Actually, I ran it for a while and there is some repetition. Also, it failed after 17 sheets' worth of numbers! I calculated it'll need at least 200
 
Upvote 0
mines still running! lol

some repetition you say? :( any solution?

this will atleast hopefully be the closest iv come to solving this problem!
 
Upvote 0
its still running! 600+ sheets created and counting! think its def stuck in a loop or something as it should not require that many sheets!!
 
Last edited:
Upvote 0
Unless that's the repetition I was talking about - must be worse than I thought. I'll take another look at this, maybe tonight, poss over the weekend. It's probably worth doing on general principles. Whatever I do it needs to be quick!
 
Upvote 0
I've got a version that saves every time a new sheet is added and 2003 seems to fall over once the file size hits 100mb. Running in 2007 it's still going strong at 173 mb and currently 8 sheets!

****, I wish I hadn't read your post!
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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