How to get all number combination from a list
 

Sub Combinations()
Dim n As Integer, m As Integer
Dim v As Variant, rng As Range
numcomb = 0
' Numbers should start in A1 and be in the first row
Set rng = Range("A1:T1") '<== change to reflect the location of your
numbers
'Set rng = rng.Resize(1, 5)
v = Application.Transpose(Application _
.Transpose(rng))
n = UBound(v, 1)
'm = InputBox("Taken how many at a time?", "Combinations")
m = 6
If Application.Combin(n, m) 64530 Then
MsgBox "Too many to write out, quitting"
Exit Sub
End If
Range("A3").Select
Comb2 n, m, 1, "'", v
End Sub

'Generate combinations of integers k..n taken m at a time, recursively
Sub Comb2(ByVal n As Integer, ByVal m As Integer, _
ByVal k As Integer, ByVal s As String, v As Variant)
Dim v1 As Variant
If m n - k + 1 Then Exit Sub
If m = 0 Then
'Debug.Print "-" & s & "<-"
v1 = Split(Replace(Trim(s), "'", ""), " ")
For i = LBound(v1) To UBound(v1)
ActiveCell.Offset(0, i) = v(v1(i))
Next
ActiveCell.Offset(1, 0).Select
Exit Sub
End If
Comb2 n, m - 1, k + 1, s & k & " ", v
Comb2 n, m, k + 1, s, v
End Sub

--
Regards,
Tom Ogilvy


" wrote:

Dear all,

I am new in excel programming. I need to get all the 6-number
combination from a list, how?

To illustration,

The list is, for example, 1,2,3,4,5,6,7,8,9,...10
The 6-number combination are
combintation 1) 1,2,3,4,5,6
combintation 2) 1,2,3,4,5,7
.....

How to get all combination?


Thanks for your inputs in advance,

Wing

How to get all number combination from a list
 

On 4ÔÂ11ÈÕ, ÏÂÎç11ʱ38·Ö, Tom Ogilvy
wrote:
Sub Combinations()
Dim n As Integer, m As Integer
Dim v As Variant, rng As Range
numcomb = 0
' Numbers should start in A1 and be in the first row
Set rng = Range("A1:T1") '<== change to reflect the location of your
numbers
'Set rng = rng.Resize(1, 5)
v = Application.Transpose(Application _
.Transpose(rng))
n = UBound(v, 1)
'm = InputBox("Taken how many at a time?", "Combinations")
m = 6
If Application.Combin(n, m) 64530 Then
MsgBox "Too many to write out, quitting"
Exit Sub
End If
Range("A3").Select
Comb2 n, m, 1, "'", v
End Sub

'Generate combinations of integers k..n taken m at a time, recursively
Sub Comb2(ByVal n As Integer, ByVal m As Integer, _
ByVal k As Integer, ByVal s As String, v As Variant)
Dim v1 As Variant
If m n - k + 1 Then Exit Sub
If m = 0 Then
'Debug.Print "-" & s & "<-"
v1 = Split(Replace(Trim(s), "'", ""), " ")
For i = LBound(v1) To UBound(v1)
ActiveCell.Offset(0, i) = v(v1(i))
Next
ActiveCell.Offset(1, 0).Select
Exit Sub
End If
Comb2 n, m - 1, k + 1, s & k & " ", v
Comb2 n, m, k + 1, s, v
End Sub

--
Regards,
Tom Ogilvy



" wrote:
Dear all,

I am new in excel programming. I need to get all the 6-number
combinationfrom a list, how?

To illustration,

The list is, for example, 1,2,3,4,5,6,7,8,9,...10
The 6-numbercombinationare
combintation 1) 1,2,3,4,5,6
combintation 2) 1,2,3,4,5,7
.....

How to get allcombination?

Thanks for your inputs in advance,

Wing- Òþ²Ø±»ÒýÓÃÎÄ×Ö -

- ÏÔʾÒýÓõÄÎÄ×Ö -

Hi Tom,

I have modified your coding a little bit before it can be run. But
after running, it produce no result even I have input the numbers in
1st row.

Could you please help to check if there is anything wrong.

Sub Combinations()
Dim n As Integer, m As Integer
Dim v As Variant, rng As Range
numcomb = 0
' Numbers should start in A1 and be in the first row
Set rng = Range("A1:M1") '<== change to reflect the location of
your numbers
Set rng = rng.Resize(1, 13)
v = Application.Transpose(Application.Transpose(rng))
n = UBound(v, 1)
'm = InputBox("Taken how many at a time?", "Combinations")
m = 6
'If Application.Combin(n, m) 64530 Then MsgBox "Too many to write
out, quitting"
' Exit Sub
'End If
'Range("A1:M1").Select
Comb2 n, m, 1, "'", v
End Sub

'Generate combinations of integers k..n taken m at a time, recursively
Sub Comb2(ByVal n As Integer, ByVal m As Integer, _
ByVal k As Integer, ByVal s As String, v As Variant)
Dim v1 As Variant
If m n - k + 1 Then Exit Sub
If m = 0 Then
'Debug.Print "-" & s & "<-"
v1 = Split(Replace(Trim(s), "'", ""), " ")
For i = LBound(v1) To UBound(v1)
ActiveCell.Offset(0, i) = v(v1(i))
Next
ActiveCell.Offset(1, 0).Select
Exit Sub
End If
Comb2 n, m - 1, k + 1, s & k & " ", v
Comb2 n, m, k + 1, s, v
End Sub

Thanks a lot,

Ali