Spencer101 said:
Look up how to use =PERMUT()
The correct count is probably COMBIN(36,6).
PERMUT() counts the number of arrangements taking order into account. That
would be appropriate for a Pick-N game. But Pick-N games typically use only
the digits 0 through 9.
COMBIN() counts the number arrangements ignoring order. That is typical of
lotto games like the Canadian Jour du Paye game, which is probably what "Ms
Nosizwe" is asking about.
In any case, that only __counts__ the number of combination.
The following macro generates all n-choose-k combinations ignoring order,
which might be what "Ms Nosizwe" meant by "work out all possible
combinations".
-----
Option Explicit
Sub combinKofN()
Dim n As Long, k As Long
Dim maxCombin As Long, nCombin As Long
Dim maxRow As Long, nRow As Long
Dim i As Long, j As Long
Dim resRng As Range
Dim st As Single, et As Single
st = Timer
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
' input parameters
n = Range("a1")
k = Range("a2")
' output location
Range("b1", Cells(1, Columns.Count)).EntireColumn.Delete
Set resRng = Range("b1")
maxCombin = WorksheetFunction.Combin(n, k)
maxRow = Rows.Count
If maxRow > maxCombin Then maxRow = maxCombin
' set of numbers 1 to n
ReDim mySet(1 To n) As Long
For i = 1 To n: mySet(i) = i: Next
' set of indexes for mySet
ReDim idx(1 To k) As Long
For i = 1 To k: idx(i) = i: Next
' results
ReDim myCombin(1 To maxRow, 1 To k) As Long
nCombin = 0: nRow = 0
Do
' record combination
nRow = nRow + 1
For i = 1 To k
myCombin(nRow, i) = mySet(idx(i))
Next
nCombin = nCombin + 1
If nCombin = maxCombin Then GoTo showResults
If nRow = maxRow Then
' output group of combinations
With resRng.Resize(nRow, k)
.Value = myCombin
.EntireColumn.AutoFit
End With
' separate groups by one column
resRng.Offset(0, k).EntireColumn.ColumnWidth = _
resRng.Offset(0, k - 1).ColumnWidth
Set resRng = resRng.Offset(0, k + 1)
nRow = 0
DoEvents
End If
' next combination
i = k: j = 0
While idx(i) = n - j
i = i - 1: j = j + 1
Wend
idx(i) = idx(i) + 1
For j = i + 1 To k
idx(j) = idx(j - 1) + 1
Next
Loop
showResults:
' output combinations
With resRng.Resize(nRow, k)
.Value = myCombin
.EntireColumn.AutoFit
End With
With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
et = Timer
MsgBox "done: " & Format(et - st, "0.000") & " sec"
End Sub