combination program (please edit)

[its_me_renan]

Below is the program that generates 20 number combination and each combination was written on column C to V. Can you please help me to edit this program in order to generate 20 number combination from 50 numbers (50C20) in which upon reaching the last row of excel (row 1048576) it will automatically proceed to other column let say column X to AQ. If sheet 1 is fully filled up it will proceeed to other sheets. Thank you.


[TABLE="width: 416"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer, G As Integer, H As Integer, I As Integer, J As Integer, K As Integer, L As Integer, M As Integer, N As Integer, O As Integer, P As Integer, Q As Integer, R As Integer, S As Integer, T As Integer, U As Integer[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Dim nNb() As Integer, V As Integer, X As Double[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Sub GetCombin()[/TD]
[/TR]
[TR]
[TD]' Program to generate 20-number combinations[/TD]
[/TR]
[TR]
[TD]Application.ScreenUpdating = False[/TD]
[/TR]
[TR]
[TD]ReDim nNb(27)[/TD]
[/TR]
[TR]
[TD]Range("A1").Select[/TD]
[/TR]
[TR]
[TD]V = 1[/TD]
[/TR]
[TR]
[TD]' Allow up to 27 numbers starting in A1 and descending[/TD]
[/TR]
[TR]
[TD]Do Until V = 28[/TD]
[/TR]
[TR]
[TD]If ActiveCell.Offset(V - 1, 0) = "" Then Exit Do[/TD]
[/TR]
[TR]
[TD]nNb(V) = ActiveCell.Offset(V - 1, 0).Value[/TD]
[/TR]
[TR]
[TD]V = V + 1[/TD]
[/TR]
[TR]
[TD]Loop[/TD]
[/TR]
[TR]
[TD]ReDim Preserve nNb(V)[/TD]
[/TR]
[TR]
[TD]V = V - 1[/TD]
[/TR]
[TR]
[TD]X = 1[/TD]
[/TR]
[TR]
[TD]' Place combinations in columns C to V starting at row 2[/TD]
[/TR]
[TR]
[TD]For A = 1 To V - 19[/TD]
[/TR]
[TR]
[TD]For B = A + 1 To V - 18[/TD]
[/TR]
[TR]
[TD]For C = B + 1 To V - 17[/TD]
[/TR]
[TR]
[TD]For D = C + 1 To V - 16[/TD]
[/TR]
[TR]
[TD]For E = D + 1 To V - 15[/TD]
[/TR]
[TR]
[TD]For F = E + 1 To V - 14[/TD]
[/TR]
[TR]
[TD]For G = F + 1 To V - 13[/TD]
[/TR]
[TR]
[TD]For H = G + 1 To V - 12[/TD]
[/TR]
[TR]
[TD]For I = H + 1 To V - 11[/TD]
[/TR]
[TR]
[TD]For J = I + 1 To V - 10[/TD]
[/TR]
[TR]
[TD]For K = J + 1 To V - 9[/TD]
[/TR]
[TR]
[TD]For L = K + 1 To V - 8[/TD]
[/TR]
[TR]
[TD]For M = L + 1 To V - 7[/TD]
[/TR]
[TR]
[TD]For N = M + 1 To V - 6[/TD]
[/TR]
[TR]
[TD]For O = N + 1 To V - 5[/TD]
[/TR]
[TR]
[TD]For P = O + 1 To V - 4[/TD]
[/TR]
[TR]
[TD]For Q = P + 1 To V - 3[/TD]
[/TR]
[TR]
[TD]For R = Q + 1 To V - 2[/TD]
[/TR]
[TR]
[TD]For S = R + 1 To V - 1[/TD]
[/TR]
[TR]
[TD]For T = S + 1 To V[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 2).Value = nNb(A)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 3).Value = nNb(B)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 4).Value = nNb(C)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 5).Value = nNb(D)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 6).Value = nNb(E)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 7).Value = nNb(F)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 8).Value = nNb(G)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 9).Value = nNb(H)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 10).Value = nNb(I)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 11).Value = nNb(J)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 12).Value = nNb(K)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 13).Value = nNb(L)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 14).Value = nNb(M)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 15).Value = nNb(N)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 16).Value = nNb(O)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 17).Value = nNb(P)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 18).Value = nNb(Q)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 19).Value = nNb(R)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 20).Value = nNb(S)[/TD]
[/TR]
[TR]
[TD]ActiveCell.Offset(X, 21).Value = nNb(T)[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]X = X + 1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Next T[/TD]
[/TR]
[TR]
[TD]Next S[/TD]
[/TR]
[TR]
[TD]Next R[/TD]
[/TR]
[TR]
[TD]Next Q[/TD]
[/TR]
[TR]
[TD]Next P[/TD]
[/TR]
[TR]
[TD]Next O[/TD]
[/TR]
[TR]
[TD]Next N[/TD]
[/TR]
[TR]
[TD]Next M[/TD]
[/TR]
[TR]
[TD]Next L[/TD]
[/TR]
[TR]
[TD]Next K[/TD]
[/TR]
[TR]
[TD]Next J[/TD]
[/TR]
[TR]
[TD]Next I[/TD]
[/TR]
[TR]
[TD]Next H[/TD]
[/TR]
[TR]
[TD]Next G[/TD]
[/TR]
[TR]
[TD]Next F[/TD]
[/TR]
[TR]
[TD]Next E[/TD]
[/TR]
[TR]
[TD]Next D[/TD]
[/TR]
[TR]
[TD]Next C[/TD]
[/TR]
[TR]
[TD]Next B[/TD]
[/TR]
[TR]
[TD]Next A[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Application.ScreenUpdating = True[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

The resulting file would require over 3000 Terabytes of storage for its nearly 3B worksheets; Excel would fall over way, way before you got there.

 

[its_me_renan]

OK. Copy.
Anyway sir, referring to the previous program, assuming i want to generate 20-number combination out of 28 numbers, what would be the command in order to proceed to other vacant column upon reaching the last row of excel (row 1048576).
Thank you.

 

[shg]

Sub x()
    Const n         As Long = 28
    Const m         As Long = 20

    Dim aiC(1 To m) As Long
    Dim wks         As Worksheet
    Dim nRow        As Long
    Dim iRow        As Long
    Dim iCol        As Long
    Dim nCol        As Long

    Set wks = Worksheets(1)
    nRow = wks.Rows.Count
    nCol = wks.Columns.Count
    iCol = 1

    aiC(1) = -1
    Application.ScreenUpdating = False

    Do While bNextCombo(aiC, n)
        iRow = iRow + 1
        If iRow > nRow Then
            iRow = 1
            iCol = iCol + m + 1
            If iCol > nCol - m + 1 Then
                iCol = 1
                Set wks = wks.Next
            End If
        End If

        wks.Cells(iRow, iCol).Resize(, m) = aiC
    Loop

    Application.ScreenUpdating = True
End Sub

Public Function bNextCombo(ByRef aiC() As Long, _
                           ByVal n As Long) As Boolean
    ' Sets aiC to the next combination of n choose m in lexical order
    ' Returns True unless the combination is the last, in which case
    ' it leaves aiC unmodified.

    ' Initializes an array with an initial value < 0 to the first combo:
    '                   {m-1,  m-2, ...,     1,   0}
    ' The last combo is {n-1,  n-2, ..., n-m+1, n-m}

    ' shg 2009-12
    '     2011-07 (modified to require aiC(0) < 0 to initialize)

    ' VBA only

    Dim m           As Long
    Dim i           As Long

    m = UBound(aiC)
    If n < m Then Exit Function

    If aiC(1) < 0 Then    ' set initial combo
        i = 1
        aiC(1) = m - 2

    Else

        ' find rightmost incrementable index
        For i = m To 2 Step -1
            If aiC(i) < aiC(i - 1) - 1 Then Exit For
        Next i
    End If

    If i <> 1 Or aiC(1) < n - 1 Then
        ' increment that index, and set 'righter' indices descending to 0
        aiC(i) = aiC(i) + 1
        For i = i + 1 To m
            aiC(i) = m - i
        Next i

        bNextCombo = True
    End If
End Function

The resulting workbook is a modest 289MB.

 

[its_me_renan]

Thank you.

 

[shg]

You're welcome.