Gilles/macro

Snowy

Member
Gilles you did this macro a while back for me, is it possible you could change/modify it for a 5/36 lotto draw, that way i will be able to see what changes you make and then do my own without having to ask you each time

Many thanks

Tim

P.S What is Dennis talking about, he seems to talk in riddles all the time?????

Option Explicit
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim N As Long
Sub List_Comb()
Range("A1").Select
Application.ScreenUpdating = False
N = 0
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
N = N + 1
If N = 65001 Then
N = 1
ActiveCell.Offset(-65000, 1).Select
Application.ScreenUpdating = True
Application.ScreenUpdating = False
End If
ActiveCell.Value = A & "-" & B & "-" & C & "-" & D & "-" & E & "-" & F
ActiveCell.Offset(1, 0).Select
Next F
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub

GillesD

Member
Combinations for 5/36

Well usually I say that what is clear in my mind, I can express clearly. So for a clear question, here is the answer, a modified macro for all combinations of a 5/36 lottery.

Option Explicit
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer
Dim N As Long
Sub List_Comb536()
Range("A1").Select
Application.ScreenUpdating = False
N = 0
For A = 1 To 32
For B = A + 1 To 33
For C = B + 1 To 34
For D = C + 1 To 35
For E = D + 1 To 36
N = N + 1
If N = 65001 Then
N = 1
ActiveCell.Offset(-65000, 1).Select
Application.ScreenUpdating = True
Application.ScreenUpdating = False
End If
ActiveCell.Value = A & "-" & B & "-" & C & "-" & D & "-" & E
ActiveCell.Offset(1, 0).Select
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub

With relatively few combinations (376,992), it could easily be modified to show one number per column instead of an output like 1-2-3-4-5-6 for the 6/49 lottery. This was necessary to show all 13,983,816 combinations on a single Excel sheet.

Snowy

Member
Cheers Gilles/99% clear

Just one little thing i dont understand is this bit below

the sub list_comb536() why has '536' been added

Cio

Tim.

GillesD

Member
5/36 lottery

After the SUB command, you must enter the name of the macro. I changed the name from List_Comb() to List_Comb536() just to differentiate it from the the original macro and since it is for a 5/36 lottery... Any name (within some limits) would have been acceptable, like XYZ(), Macro_1(), Snowy(), etc.

surinder

Member
Excel help

Hi

I need excel 2007 macro help for 6/49?

Lets says I import 10,000 lines in column A to F one no in each column and next in column G to M( 7 columns) I have few nos in 3 lines (rows). next in column N in front of each line from G to M I have nos. 1,2,1(how many nos to be matched)
What I need is first line in G to M match against A to F and accept lines only which match one no from G to M(as in column N it says "1")all other lines are rejected.

I move to second line from G to M which says "2" and try to match from the lines selected above after the use of first line here it again reject some more lines.
Then we move to the third line from G to M which again says "1" and try to match 1 no from the remaining lines above and finally we get few final lines.

Then we select another group in column G to M this time we have 4 rows of seven nos and it says in column N 2,1,1,2 and we start the same process of selecting lines using one line from G to M using (2,1,1,2) and add the final list of lines in the first group lines

Same way we keep on using the different groups and keep on adding the lines in the final group of lines.

Any help from excel guru's

Thanks