GillesD, Problem with a macro of yours?

[mremixer]

Hi Gillesd?

Hope you see this. I came across this macro of yours

Option Explicit
Public A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Sub List_Comb()
' Macro to list combinations in an Excel sheet
Dim N As Long, nMinA As Integer, nMaxF As Integer
Sheets("List_Comb").Select
Range("A1").Select
Application.ScreenUpdating = False
N = 1
Selection.ColumnWidth = 18
ActiveCell.Value = "Comb."
nMinA = 1
nMaxF = 49
' Start of loops for 6 variables
For A = nMinA To nMaxF - 5
For B = A + 1 To nMaxF - 4
For C = B + 1 To nMaxF - 3
For D = C + 1 To nMaxF - 2
For E = D + 1 To nMaxF - 1
For F = E + 1 To nMaxF
If N = 65001 Then
Selection.ColumnWidth = 18
N = 1
ActiveCell.Offset(-65000, 1).Select
Application.ScreenUpdating = True
Application.ScreenUpdating = False
ActiveCell.Value = "Comb."
End If
Next F
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub

I'm not big on macros or programming, I have a little knowledge, some from my past and some from invaluable insights thanks to your macros I have found on this forum but with the above when I ran it I got a subscript out of range error. Then while stepping through to see if I could figure out the problem I got the same with error 9. Not one to be deterred I read through the proggy and noticed the "Sheets("List_Comb").Select" (no idea what it is or does but kinda took a wild guess at my sheet needing to be named List_Comb) so I tried that and I thought that was it, but within seconds cell A1 had Comb. in it but it was obvious the macro had finished!

Having limited knowledge on the subject I am now totally lost as to how to get it to work. Any pointers or a working macro would be much appreciated.

 

[GillesD]

My macro corrected

You are right about the name of the sheet to list combinations. I hate names like Sheet1, Sheet2, etc. (actually in my case with a French version of Excel, it is Feuil1, ...), so I give my sheets names related to what they contain: Data where data is stored or List_Comb where combination are listed. So either name a sheet List_Comb or cut the line from the macro and the combinations will be listed on the active sheet when you start the macro.

As far as stopping rapidly, you are also right but this is due to the fact the lines for listing the combinations were somehow cut.

After the block of lines
If N = 65001 Then
...
End If

Add the lines:

ActiveCell.Offset(1, 0).Select
N = N + 1
ActiveCell.Value = Application.WorksheetFunction.Text(A, "00") & "-" _
& Application.WorksheetFunction.Text(B, "00") & "-" _
& Application.WorksheetFunction.Text(C, "00") & "-" _
& Application.WorksheetFunction.Text(D, "00") & "-" _
& Application.WorksheetFunction.Text(E, "00") & "-" _
& Application.WorksheetFunction.Text(F, "00")

This will put the combinations in form "01-02-03-04-05-06", in individual cells, with 65,000 of them per column.

Depending on your computer, this may take some time to list all combinations as the screen is not refreshed. This is the result of the line "Application.ScreenUpdating = False"

Hope it works, if not come back again.

 

[mremixer]

Big TQ!

Yeah that got it! Gilles! Thanks muchly! I am still re-learning about these macro's etc.. and thats one of the reasons I joined here! I'm not exactly a lottery player but I do find the stat's involved fascinating and want to learn more about the macro's used for examining & producing them! It just occured to me one day that the lottery was the perfect place to look!

I will probably be asking more at some stage but as I want to teach myself I will be using your macros as a basis! Is that OK with you? I mean I want to add & maybe take away from your macros to find out what things do i.e. the sheet select command mentioned previously.

Hope thats ok with you? As a by note I have just read through a very old post that I found fascinating, you contributed a lot to it! I have posted under it about maybe someone going back and rechecking the predictions claimed just to see how they panned out over time, I hope you get chance to review it as I found your contributions with facts & figures to be the most enlightening & I would be interested to find out your take on it now that a significant period has passed.

Thanks for the reply & the macro/s.

 

[GillesD]

Learning macros

No problem, mremixer

That's exactly how I learned macros: trying things in relation to loteries. And now a part of my annual consulting income is coming from providing services in Excel.

So feel free to use my macros and modify them to fit your needs, that's the fun way to learn.