Add this function to your workbook:
Code:
--------------------
Public Function NextComboWF(vInx As Variant, n As Long) As Long()
' Worksheet version
' Example usage for 8 choose 4:
' A B C D
' 1 2 3 4 ' literals for first combination
' 1 2 3 5 ' = NextComboWF(A2:D2, 8) array-entered and copied down
' Returns the next combination in lexical order
Dim aiInx() As Long
Dim i As Long
Dim m As Long
m = WorksheetFunction.Count(vInx)
Dim bWrap As Boolean
ReDim aiInx(1 To m)
For i = 1 To m
aiInx(i) = vInx(i)
Next
' set initial combo if empty
If aiInx(1) = 0 Then
For i = 1 To m
aiInx(i) = i
Next i
NextComboWF = aiInx
Exit Function
End If
' find rightmost incrementable index
For i = m To 1 Step -1
If aiInx(i) < n - m + i Then Exit For
Next i
If i = 0 Then
bWrap = True
i = 1
aiInx(1) = 0
End If
' set 'righter' indices sequentially beyond
aiInx(i) = aiInx(i) + 1
For i = i + 1 To m
aiInx(i) = aiInx(i - 1) + 1
Next
NextComboWF = aiInx
End Function
--------------------
In A1, B1, and C1, enter 1, 2, and 3 respectively.
In A2:C2, array enter =NextComboWF(A1:C1, 8) and copy down to row 56.
(Array formulas MUST be confirmed with Ctrl+Shift+Enter, not Enter.
You'll know you did it correctly if curly braces appear around the
formula in the Formula Bar; you cannot type in the braces directly.)
--
shg
------------------------------------------------------------------------
shg's Profile:
http://www.thecodecage.com/forumz/member.php?userid=13
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=27126