宏可以在Excel表格中的各个列中进行数据的全部组合 [英] Macro to make all possible combinations of data in various columns in excel sheet
本文介绍了宏可以在Excel表格中的各个列中进行数据的全部组合的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
ABC
abc,def,ghi,jkl 1,2, 3 a1,e3,h5,j8
我希望以下列格式。 p>
abc 1 a1
abc 2 a1
abc 3 a1
abc 1 e3
abc 2 e3
abc 3 h5
等等,以使所有可能的组合。
任何帮助都会很棒。谢谢
解决方案
替代方法
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'(如果需要更改表)
arrA =拆分(.Range(A1),,)
arrB =拆分(.Range(B1),, )
arrC = Split(.Range(C1),,)
对于lngA = LBound(arrA)到UBound(arrA)
对于lngB = (arrB)到UBound(arrB)
对于lngC = LBound(arrC)到UBound(arrC)
.Range(I& .Rows.Count).End(xlUp) .Offset(1,0).Value = arrA(lngA)& & arrB(lngB)& & arrC(lngC)
下一页lngC
下一页lngB
下一页lngA
结束
End Sub
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
解决方案
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
这篇关于宏可以在Excel表格中的各个列中进行数据的全部组合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文