Excell Question for Gilles

[Sandman]

Hello Gilles, were you able to figure out a formula for using less than 6 numbers?? Example: playing 3, 4 or 5 numbers out of 15 possible numbers ect...? I was able to manipulate your formula for 6 numbers out 15 in whick gives you 5005 combinations. If I were to use 6 out of 14 numbers it works out to 3003 combinations if I remember correctly. Thanx for your tutorial help with Excell, it's a great tool to have if you know how to use it even a little bit

 

[GillesD]

Combinations with 3,4,5 numbers

The various macros listed below will generate the various 3, 4 and 5 combinations out of certain numbers. It is not the best programming but it works. In all cases, use the set-up described in a previous post

A - For 3-numbers combinations

No limit for the amount of numbers chosen (up to 49). The macro is:

Option Explicit
Option Base 1
Sub Combin_3N()
Dim D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(49) As Integer
Application.ScreenUpdating = False
Range("A1").Select
J = 1
Do While ActiveCell > 0
vN(J) = ActiveCell.Value
J = J + 1
ActiveCell.Offset(1, 0).Select
Loop
J = J - 1
Range("C1").Select
I = 1
For D = 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(D)
ActiveCell.Offset(0, 2).Value = vN(E)
ActiveCell.Offset(0, 3).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Next F
Next E
Next D
Range("A1").Select
End Sub

B - For 4-numbers combinations

No limit for the amount of numbers chosen (up to 49). The macro is:

Option Explicit
Option Base 1
Sub Combin_4N()
Dim C As Integer, D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(49) As Integer
Application.ScreenUpdating = False
Range("A1").Select
J = 1
Do While ActiveCell > 0
vN(J) = ActiveCell.Value
J = J + 1
ActiveCell.Offset(1, 0).Select
Loop
J = J - 1
Range("C1").Select
I = 1
For C = 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(C)
ActiveCell.Offset(0, 2).Value = vN(D)
ActiveCell.Offset(0, 3).Value = vN(E)
ActiveCell.Offset(0, 4).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001
ActiveCell.Offset(-60000, 6).Select
End Select
Next F
Next E
Next D
Next C
Range("A1").Select
End Sub

C - For 5-numbers combinations

There is a limit of 40 for the amount of numbers chosen. The macro is:


Option Explicit
Option Base 1
Sub Combin_5N()
Dim B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim I As Long, J As Integer, vN(40) As Integer
Application.ScreenUpdating = False
Range("A1").Select
J = 1
Do While ActiveCell > 0
vN(J) = ActiveCell.Value
J = J + 1
ActiveCell.Offset(1, 0).Select
Loop
J = J - 1
Range("C1").Select
I = 1
For B = 1 To J - 4
For C = B + 1 To J - 3
For D = C + 1 To J - 2
For E = D + 1 To J - 1
For F = E + 1 To J
ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(B)
ActiveCell.Offset(0, 2).Value = vN(C)
ActiveCell.Offset(0, 3).Value = vN(D)
ActiveCell.Offset(0, 4).Value = vN(E)
ActiveCell.Offset(0, 5).Value = vN(F)
I = I + 1
ActiveCell.Offset(1, 0).Select
Select Case I
Case 60001, 120001, 180001, 240001, 300001, 360001, 420001, 480001, 540001, 600001
ActiveCell.Offset(-60000, 7).Select
End Select
Next F
Next E
Next D
Next C
Next B
Range("A1").Select
End Sub