vba 宏在excel表中的各个列中进行所有可能的数据组合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5729967/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Macro to make all possible combinations of data in various columns in excel sheet
提问by UJ9
I am a complete novice at macros and have been given a task which if done manually, can take a lot of time. I have a worksheet which has data as below:
我是宏的完全新手，并且已经完成了一项任务，如果手动完成，可能需要很多时间。我有一个工作表，其中包含如下数据：
A B C
abc,def,ghi,jkl 1,2,3 a1,e3,h5,j8
I would like this to be in the following format.
我希望采用以下格式。
abc 1 a1
abc 2 a1
abc 3 a1
abc 1 e3
abc 2 e3
abc 3 h5
and so on to make all possible combinations. Any help will be great. Thanks
等等以进行所有可能的组合。任何帮助都会很棒。谢谢
回答by osknows
Alternative method
替代方法
Private Sub Combinations()
Dim arrA() As String, arrB() As String, arrC() As String
Dim lngA As Long, lngB As Long, lngC As Long
With Sheet1 '(CHANGE SHEET IF REQUIRED)
arrA = Split(.Range("A1"), ",")
arrB = Split(.Range("B1"), ",")
arrC = Split(.Range("C1"), ",")
For lngA = LBound(arrA) To UBound(arrA)
For lngB = LBound(arrB) To UBound(arrB)
For lngC = LBound(arrC) To UBound(arrC)
.Range("I" & .Rows.Count).End(xlUp).Offset(1, 0).Value = arrA(lngA) & " " & arrB(lngB) & " " & arrC(lngC)
Next lngC
Next lngB
Next lngA
End With
End Sub
回答by Excellll
This code will take the data from columns A, B, and C, and give the output you described in columns E, F, and G.
此代码将从 A、B 和 C 列中获取数据，并提供您在 E、F 和 G 列中描述的输出。
Sub combinations()
Dim c1() As Variant
Dim c2() As Variant
Dim c3() As Variant
Dim out() As Variant
Dim j, k, l, m As Long
Dim col1 As Range
Dim col2 As Range
Dim col3 As Range
Dim out1 As Range
Set col1 = Range("A1", Range("A1").End(xlDown))
Set col2 = Range("B1", Range("B1").End(xlDown))
Set col3 = Range("C1", Range("C1").End(xlDown))
c1 = col1
c2 = col2
c3 = col3
Set out1 = Range("E2", Range("G2").Offset(UBound(c1) * UBound(c2) * UBound(c3)))
out = out1
j = 1
k = 1
l = 1
m = 1
Do While j <= UBound(c1)
Do While k <= UBound(c2)
Do While l <= UBound(c3)
out(m, 1) = c1(j, 1)
out(m, 2) = c2(k, 1)
out(m, 3) = c3(l, 1)
m = m + 1
l = l + 1
Loop
l = 1
k = k + 1
Loop
k = 1
j = j + 1
Loop
out1.Value = out
End Sub
If on the other hand, your data is comma-separated in cells A1, B1, and C1, the following code will work similarly.
另一方面，如果您的数据在单元格 A1、B1 和 C1 中以逗号分隔，则以下代码的工作方式类似。
Sub combinations()
Dim c1() As String
Dim c2() As String
Dim c3() As String
Dim out() As Variant
Dim j, k, l, m As Long
Dim col1 As Range
Dim col2 As Range
Dim col3 As Range
Dim out1 As Range
Set col1 = Range("A1")
Set col2 = Range("B1")
Set col3 = Range("C1")
c1 = Split(col1.Value, ",")
c2 = Split(col2.Value, ",")
c3 = Split(col3.Value, ",")
Set out1 = Range("E1", Range("G1").Offset((UBound(c1) + 1) * (UBound(c2) + 1) * (UBound(c3) + 1)))
out = out1
j = 0
k = 0
l = 0
m = 1
Do While j <= UBound(c1)
Do While k <= UBound(c2)
Do While l <= UBound(c3)
out(m, 1) = c1(j)
out(m, 2) = c2(k)
out(m, 3) = c3(l)
m = m + 1
l = l + 1
Loop
l = 0
k = k + 1
Loop
k = 0
j = j + 1
Loop
out1.Value = out
End Sub
