Question

怎么用VBA在EXCEL中解决排列组合的问题

如图，有P1到P6，每个P后面有几个不等的数值，这里P1有一个（1），p2有2个（4，6），p3有2个（2，3），p4有3个（9，10, 11）p5有2个（7，8），p6有2个（12）。要解决的是在P1，p2，p3,p4,p5,p6中各取一个数，排列组合成如图中8-13行中所示的组合，一共应该有1*2*2*3*2*1=24种组合，我写了3组。请问，如何用VBA写个代码来做这个工作，因为以后每个P后面的数值的个数会有不同，组合会更多，。谢谢啦！

Answer 1

Sub a()
Dim a1, a2, a3, a4, a5, a6, b, c
b = WorksheetFunction.CountA(Rows(1))
For a1 = 2 To 6
If WorksheetFunction.CountA(Rows(a1)) > b _
Then b = WorksheetFunction.CountA(Rows(a1))
Next
c = 2
For a1 = 2 To b
If Cells(1, a1) <> "" Then
For a2 = 2 To b
If Cells(2, a2) <> "" Then
For a3 = 2 To b
If Cells(3, a3) <> "" Then
For a4 = 2 To b
If Cells(4, a4) <> "" Then
For a5 = 2 To b
If Cells(5, a5) <> "" Then
For a6 = 2 To b
If Cells(6, a6) <> "" Then
Cells(8, c) = Cells(1, a1)
Cells(9, c) = Cells(2, a2)
Cells(10, c) = Cells(3, a3)
Cells(11, c) = Cells(4, a4)
Cells(12, c) = Cells(5, a5)
Cells(13, c) = Cells(6, a6)
c = c + 1

追答

c＝c + 1
End If
Next
End If
Next
End If
Next
End If
Next
End If
Next
End If
Next
End Sub

手机APP，有字数限制……