Eliminating some combinations
Eliminating some combinations can be done but the VBA program given may not be the best choice since combinations are listed one combination per cell.
The VBA program listed at the end of this post (List_CombA) might be more appropriate. It does the following:
1 – it provides some headings for columns (columns named N1 to N6, then a column named Test, then a blank column, before repeating)
2 – the combinations are listed one per row but each number of a combination (N1 to N6) is in a different cell over 6 columns
You can then use the column Test to enter a specific condition to be used for eliminating some combinations. To identify combinations with 3 consecutive numbers (whether 1-2-3, 23-24-25 or 47-48-49), in cell G2, you could enter the formula: =OR(A2=C2-2,B2=D2-2,C2=D2-2,D2=F2-2) and then copy it down and also in columns O and W. Cells will take the value TRUE if there are three consecutive numbers in the combination and FALSE otherwise. The filtering options of Excel can then be used to eliminate those you do not want to keep.
The VBA program is:
Option Explicit
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim N As Long
Sub List_CombA()
Range("A1").Select
Application.ScreenUpdating = False
N = 0
ActiveCell.Offset(0, 0).Value = "N1"
ActiveCell.Offset(0, 1).Value = "N2"
ActiveCell.Offset(0, 2).Value = "N3"
ActiveCell.Offset(0, 3).Value = "N4"
ActiveCell.Offset(0, 4).Value = "N5"
ActiveCell.Offset(0, 5).Value = "N6"
ActiveCell.Offset(0, 6).Value = "Test"
ActiveCell.Offset(1, 0).Select
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
If A + B + C + D + E + F = 150 Then
If N = 65001 Then
N = 0
ActiveCell.Offset(-65002, 8).Select
Application.ScreenUpdating = True
Application.ScreenUpdating = False
ActiveCell.Offset(0, 0).Value = "N1"
ActiveCell.Offset(0, 1).Value = "N2"
ActiveCell.Offset(0, 2).Value = "N3"
ActiveCell.Offset(0, 3).Value = "N4"
ActiveCell.Offset(0, 4).Value = "N5"
ActiveCell.Offset(0, 5).Value = "N6"
ActiveCell.Offset(0, 6).Value = "Test"
ActiveCell.Offset(1, 0).Select
End If
ActiveCell.Offset(0, 0).Value = A
ActiveCell.Offset(0, 1).Value = B
ActiveCell.Offset(0, 2).Value = C
ActiveCell.Offset(0, 3).Value = D
ActiveCell.Offset(0, 4).Value = E
ActiveCell.Offset(0, 5).Value = F
ActiveCell.Offset(1, 0).Select
N = N + 1
End If
Next F
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub
Notes:
- this program is not optimized in any way but it is just a quick and dirty way of obtaining some results
- this program should not be used if more than 2,000,000 combinations are expected to be listed; you will run out of columns in the worksheet
- it might be better if you set column width to 5 for columns A to Z