Microsoft Office Tutorials and References
In Depth Information
LIST COMBINATIONS OF N ITEMS TAKEN M AT A TIME
Sub ClumsyCombin()
Dim n As Integer
n = InputBox("Number of items?", "Combinations")
NumComb = 0
Range("A:A").ClearContents
Range("A1").Select
For i = 1 To n-2
For j = 2 To n-1
For k = 3 To n
If i < j And j < k Then
ActiveCell = i & " " & j & " " & k
ActiveCell.Offset(1, 0).Select
NumComb = NumComb + 1
End If
Next k
Next j
Next i
MsgBox (NumComb & " combinations listed")
End Sub
A major problem with this approach is that the number of For...Next loops
is hard-coded in the routine. If you need to ﬁ nd combinations of, say, 4 items at
a time, an additional For...Next loop has to be inserted, and the condition
check needs to be modiﬁ ed. For combinations of 2, a loop would need to be
removed or skipped.
A Better Solution
This problem is an ideal candidate for a recursive function. This solution
comprises a subroutine to specify the inputs n (population set size) and m
(subset size), initialize a combinations counter, and set things up for entry of
the combinations in column A of the active worksheet. This routine then calls
a recursive function to generate the combinations. After all combinations are
generated, the program exits, with a message with information on the number
of combinations found.
Copy the following code to a blank module in a workbook:
Dim NumComb ‘Combinations counter
Sub Combinations()
Dim n As Integer, m As Integer
NumComb = 0
n = InputBox("Number of items?", "Combinations")
m = InputBox("Taken how many at a time?", "Combinations")

Search JabSto ::

Custom Search