VBA - 编写 4 列数据的所有可能组合 [英] VBA - Write all possible combinations of 4 columns of data
问题描述
我找到了为 3 列数据编写所有可能组合的脚本,但我正在尝试修改代码以编写 4 列和可能的 5 列,但我不确定如何.如果有人可以提供帮助,那就太好了!我已经尝试通过添加额外的变量来做我认为应该工作的事情(我认为他们会合乎逻辑地去那里),但是我得到了一个我无法解释的编译错误:没有循环".
I've found script for writing all the possible combinations for 3 columns of data but am trying to modify the code to write 4 columns and possibly 5 and am not sure how. If anyone can help that would be great! I've tried doing what I think should work by adding in extra variables where they would follow (where I think they would go logically) but am geting a "Compile Error: Do without loop" that I cant explain.
这是来自用户 Excellll 的 3 列(未经我的修改)的代码.
Here is the code for the 3 columns (without my modifications) from User Excellll.
代码的描述在这里:这段代码将从 A、B 和 C 列中获取数据,并给出您在 E、F 和 G 列中描述的输出."
The description of the code is here:"This code will take the data from columns A, B, and C, and give the output you described in columns E, F, and 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
预先感谢您的帮助
推荐答案
For 5 Columns
For 5 Columns
Sub combinations()
Dim c1() As Variant
Dim c2() As Variant
Dim c3() As Variant
Dim c4() As Variant
Dim c5() As Variant
Dim out() As Variant
Dim j As Long, k As Long, l As Long, m As Long, n As Long, o As Long
Dim col1 As Range
Dim col2 As Range
Dim col3 As Range
Dim col4 As Range
Dim col5 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))
Set col4 = Range("D1", Range("D1").End(xlDown))
Set col5 = Range("E1", Range("E1").End(xlDown))
c1 = col1
c2 = col2
c3 = col3
c4 = col4
c5 = col5
Set out1 = Range("G2", Range("K2").Offset(UBound(c1) * UBound(c2) * UBound(c3) * UBound(c4) * UBound(c5)))
out = out1
j = 1
k = 1
l = 1
m = 1
n = 1
o = 1
Do While j <= UBound(c1)
Do While k <= UBound(c2)
Do While l <= UBound(c3)
Do While m <= UBound(c4)
Do While n <= UBound(c5) ' This now loops correctly
out(o, 1) = c1(j, 1)
out(o, 2) = c2(k, 1)
out(o, 3) = c3(l, 1)
out(o, 4) = c4(m, 1)
out(o, 5) = c5(n, 1)
o = o + 1
n = n + 1
Loop
n = 1
m = m + 1
Loop
m = 1
l = l + 1
Loop
l = 1
k = k + 1
Loop
k = 1
j = j + 1
Loop
out1.Value = out
End Sub
对于 4 列
Sub combinations()
Dim c1() As Variant
Dim c2() As Variant
Dim c3() As Variant
Dim c4() As Variant
Dim out() As Variant
Dim j As Long, k As Long, l As Long, m As Long, n As Long
Dim col1 As Range
Dim col2 As Range
Dim col3 As Range
Dim col4 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))
Set col4 = Range("D1", Range("D1").End(xlDown))
c1 = col1
c2 = col2
c3 = col3
c4 = col4
Set out1 = Range("G2", Range("K2").Offset(UBound(c1) * UBound(c2) * UBound(c3) * UBound(c4)))
out = out1
j = 1
k = 1
l = 1
m = 1
n = 1
Do While j <= UBound(c1)
Do While k <= UBound(c2)
Do While l <= UBound(c3)
Do While m <= UBound(c4)
out(n, 1) = c1(j, 1)
out(n, 2) = c2(k, 1)
out(n, 3) = c3(l, 1)
out(n, 4) = c4(m, 1)
n = n + 1
m = m + 1
Loop
m = 1
l = l + 1
Loop
l = 1
k = k + 1
Loop
k = 1
j = j + 1
Loop
out1.Value = out
End Sub
这篇关于VBA - 编写 4 列数据的所有可能组合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!