excel排列组合问题

zbj7110

怎样使用excel vba列出所有组合情况：
例如，有三个集合A,B,C  A={2,5,7,8,9,10} B={12,14,15,17,18} C={21,22,24,29,30,31,}
分别从三个集合各取2个数，得到一个6个数的组合
怎样使用vba代码列出所有组合，且这6个数每1个数显示在1列中

jxb8088

Sub jxb8088()
Dim arr() As String
r = 0
A = Array(2, 5, 7, 8, 9, 10)
B = Array(12, 14, 15, 17, 18)
C = Array(21, 22, 24, 29, 30, 31)
For a1 = 0 To 4
For a2 = a1 + 1 To 5
For b1 = 0 To 3
For b2 = b1 + 1 To 4
For c1 = 0 To 4
For c2 = c1 + 1 To 5
ReDim Preserve arr(r) As String
arr(r) = Join(Array(A(a1), A(a2), B(b1), B(b2), C(c1), C(c2)), " ")
r = r + 1
Next
Next
Next
Next
Next
Next
Sheets(1).[a1].Resize(r, 1) = Application.Transpose(arr)
End Sub

zbj7110

非常感谢

zbj7110

如果A集合取3个数，B集合取2个数，C集合取1个数，又要如何改代码
谢谢

jxb8088

Sub jxb8088()
Dim arr() As String
r = 0
A = Array(2, 5, 7, 8, 9, 10)
B = Array(12, 14, 15, 17, 18)
C = Array(21, 22, 24, 29, 30, 31)
For a1 = 0 To 3
For a2 = a1 + 1 To 4
For a3 = a2 + 1 To 5
For b1 = 0 To 3
For b2 = b1 + 1 To 4
For c1 = 0 To 5
ReDim Preserve arr(r) As String
arr(r) = Join(Array(A(a1), A(a2), A(a3), B(b1), B(b2), C(c1)), " ")
r = r + 1
Next: Next: Next: Next: Next: Next
Sheets(1).[a1].Resize(r, 1) = Application.Transpose(arr)
End Sub

ABC12398

太感谢了！jxb8088绝对是高手!!!

ABC12398

jxb8088高手所作的VBA肯定是很多人梦寐以求的绝佳答案！！！