Note that 7 deep is the limits of Excel's capability.

Which upon searching I now believe is not true and applies to nested
worksheet if's and note code which (I think) can be nested as deeply as you
want but I stand correction on this.

Mike


"Mike H" wrote:

Hi,

As you will see the code works with 6-deep nested if loops so to produce
all combinations of 7 numbers you need to nest 1 deeper like this. Note that
7 deep is the limits of Excel's capability.

I highlighted the additional code with '****

Sub thelottery()
Application.ScreenUpdating = False
Count = 1
col = 2
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("A1:A" & lastrow)
For Each c In myRange
numbers = numbers + 1
Next
Dim n(49)
For p = 1 To numbers
n(p) = Cells(p, 1).Value
Next
For i = 1 To numbers
For j = 1 To numbers
If j <= i Then GoTo 100
For k = 1 To numbers
If k <= j Then GoTo 200
For l = 1 To numbers
If l <= k Then GoTo 300
For m = 1 To numbers
If m <= l Then GoTo 400
For o = 1 To numbers
If o <= m Then GoTo 500
'***
For p = 1 To numbers
If p <= o Then GoTo 600
'****

For x = 1 To numbers
If i = x Then firstno = n(x)
Next
For x = 1 To numbers
If j = x Then secondno = n(x)
Next
For x = 1 To numbers
If k = x Then thirdno = n(x)
Next
For x = 1 To numbers
If l = x Then fourthno = n(x)
Next
For x = 1 To numbers
If m = x Then fifthno = n(x)
Next
For x = 1 To numbers
If o = x Then sixthno = n(x)
Next
'***
For x = 1 To numbers
If p = x Then seventhno = n(x)
Next
'***
Cells(Count, col).Value = firstno & "," & secondno &
"," & thirdno & "," & fourthno & "," & fifthno & "," & sixthno & "," &
seventhno
If Count = 65536 Then
total = total + Count
Count = 1
col = col + 1
Else
Count = Count + 1
End If

600 Next
500 Next
400 Next
300 Next
200 Next
100 Next
Next
Cells(1, 8).Value = (total + Count) - 1
Application.ScreenUpdating = True
End Sub



"Kobus" wrote:

No, I would rather want a 7 ball 49 combo. Our lottery runs the 7th ball as
a bonus ball. That might reduce the odds, but I would need the experts to
edit it for me. I am making the attempt now though

"Meebers" wrote:

What about a 6 ball 52 number combo. changes = Dim n(52) and ?

"Kobus" wrote in message
...
Yes, I figured it out, but thanks again, now I will have to work on some
probabilities to reduce the 256k options

"Mike H" wrote:

Hi,

There's no syntax error but what has happened in posting is 2 lines have
wrapped

this is one line
If firstno + secondno + thirdno + fourthno + fifthno + sixthno = 138 Then


and these 2 should be on one line
Cells(Count, col).Value = firstno & "," & secondno & "," & thirdno & ","
&
fourthno & "," & fifthno & "," & sixthno

Mike


"Kobus" wrote:

There is a sintex error, with some hard work on my side I will
eventually
figure it out. You guys are just lightyears ahead with your VB
knowledge!!
Thanks

"Mike H" wrote:

Hi,

An interesting Sunday afternoon exercise. Firstly, you will need to
be quite
wealthy to cover all of the 156004 combinations that add up to 138.
To
generate them use an empty worksheet and put the numbers 1 - 49 in
column A
starting in A1. Then right click the sheet tab, view code and paste
this in
and run it and then make a cup of tea because it took several minutes
to run
on my PC.

P.S. It would be unwise to press the print button unless you have
lots of
paper!!

Sub thelottery()
Count = 1
col = 2
lastrow = Range("A65536").End(xlUp).Row
Set myRange = Range("A1:A" & lastrow)
For Each c In myRange
numbers = numbers + 1
Next
Dim n(49)
For p = 1 To numbers
n(p) = Cells(p, 1).Value
Next
For i = 1 To numbers
For j = 1 To numbers
If j <= i Then GoTo 100
For k = 1 To numbers
If k <= j Then GoTo 200
For l = 1 To numbers
If l <= k Then GoTo 300
For m = 1 To numbers
If m <= l Then GoTo 400
For o = 1 To numbers
If o <= m Then GoTo 500
For x = 1 To numbers
If i = x Then firstno
= n(x)
Next
For x = 1 To numbers
If j = x Then
secondno = n(x)
Next
For x = 1 To numbers
If k = x Then thirdno
= n(x)
Next
For x = 1 To numbers
If l = x Then
fourthno = n(x)
Next
For x = 1 To numbers
If m = x Then fifthno
= n(x)
Next
For x = 1 To numbers
If o = x Then sixthno
= n(x)
Next
If firstno + secondno + thirdno + fourthno +
fifthno +
sixthno = 138 Then
Cells(Count, col).Value = firstno & "," &
secondno &
"," & thirdno & "," & fourthno & "," & fifthno & "," & sixthno
If Count = 65536 Then
Count = 1
col = col + 1
Else
Count = Count + 1
End If
End If
500 Next
400 Next
300 Next
200 Next
100 Next

Next
Cells(1, 8).Value = Count - 1
End Sub


Mike

"Kobus" wrote:

Question1: How do I calculate the number of combinations in a 49
number 6
ball lottery where every combinations adds up to say: 138. Sum
total 279,
for instance, can only happen once.
Question2: How do I generate these results?