1、如下' 定义所有元素 函数表达在多个工作表VBA中均有使用，现想从原有("AB~AO", ",")14位英文字母增添至("AB~AWJ", ",")1257位英文字母。
elements = Split("AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,MM,AN,AO", ",")
2、不想全部编写显示，会使程序不美观和容易出现错误（如图1.
）。
3、总体来说就是：能在不全部编写1257位英文字母情况下替换：elements = Split("AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,MM,AN,AO", ",") 此表达式。
Sub GenerateCombinations()
Dim elements() As String
Dim combinations() As String
Dim result As String
Dim i As Integer, j As Integer, k As Integer
Dim rowCount As Long
Dim combinationCount As Long
' 定义所有元素
elements = Split("AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,MM,AN,AO", ",")
combinationCount = WorksheetFunction.Combin(UBound(elements) + 1, 8)
ReDim combinations(1 To combinationCount)
rowCount = 1
' 生成组合
For i = 0 To UBound(elements) - 7
For j = i + 1 To UBound(elements) - 6
For k = j + 1 To UBound(elements) - 5
For a = k + 1 To UBound(elements) - 4
For b = a + 1 To UBound(elements) - 3
For c = b + 1 To UBound(elements) - 2
For d = c + 1 To UBound(elements) - 1
For e = d + 1 To UBound(elements)
combinations(rowCount) = "AA" & "," & elements(i) & "," & elements(j) & "," & elements(k) & "," & elements(a) & "," & elements(b) & "," & elements(c) & "," & elements(d) & "," & elements(e)
rowCount = rowCount + 1
Next e
Next d
Next c
Next b
Next a
Next k
Next j
Next i
' 输出组合到单元格
With ThisWorkbook.Sheets("分析")
.Cells(1, "L") = "编号"
.Cells(1, "M") = "组合"
.Range("M2").Resize(combinationCount).Value = Application.Transpose(combinations)
For i = 1 To UBound(combinations)
.Cells(i + 1, "L") = i
Next i
End With
End Sub
|
楼主: boy23
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2024-4-16 08:31
楼主