我在 VBA 方面不太有經驗，但在網上找到了一個代碼，可以在 Excel 表中創建組合。這很好，除了我需要一個規則，它應該在空時跳過組合生成器中的單元格。我嘗試了幾種設定，但它一遍又一遍地給我相同的結果。
所以如果我有下表：
| 表格1 | 表 2 |
|---|---|
| 1 | 一種 |
| 乙 | |
| 3 | C |
結果應該是：
- 1-a
- 1-b
- 1-c
- 3-a
- 3-b
- 3-c
然而，它導致：
- 1-a
- 1-b
- 1-c
- -一種
- -b
- -C
- 3-a
- 3-b
- 3-c
任何人都可以給我一個提示或想法，看看這是否可以解決？在投入太多時間之前，很想知道什么是可能的。找到下面的 VBA。提前致謝！
Sub CombinationGenerator()
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("A2:A6") 'First column data
Set xDRg2 = Range("B2:B2") 'Second column data
Set xDRg3 = Range("C2:C2") 'Third column data
xStr = "-" 'Separator
Set xRg = Range("E2") 'Output cell
'Creating combinations
For xFN1 = 1 To xDRg1.Count
If Cells(xFN1, "A") <> "" Then 'Ignore empty cells
xSV1 = xDRg1.Item(xFN1).Text
For xFN2 = 1 To xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Text
For xFN3 = 1 To xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Text
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
Set xRg = xRg.Offset(1, 0)
Next
Next
End If
Next
End Sub
uj5u.com熱心網友回復：
xFN1迭代從1到xDRg1.Cells.Count，但第一行xDRg1是2。所以，當你行重建期間的范圍內Cells(xFN1, "A")你把1，2，3而不是2，3，4行號。
為了避免混淆這樣的代碼，我建議使用 Range.Cells 集合將 For 回圈切換為For Each回圈，這意味著回圈元素將是一個單元格（范圍物件）而不是行號。
Sub CombinationGenerator()
Dim xDRg1 As Range, xDRg2 As Range, xDRg3 As Range
Dim xRg As Range
Dim xStr As String
Dim xFN1 As Range, xFN2 As Range, xFN3 As Range
Dim xSV1 As String, xSV2 As String, xSV3 As String
Set xDRg1 = Range("A2:A6") 'First column data
Set xDRg2 = Range("B2:B2") 'Second column data
Set xDRg3 = Range("C2:C2") 'Third column data
xStr = "-" 'Separator
Set xRg = Range("E2") 'Output cell
'Creating combinations
For Each xFN1 In xDRg1.Cells
If xFN1 <> "" Then 'Ignore empty cells
xSV1 = xFN1.Text
For Each xFN2 In xDRg2.Cells
xSV2 = xFN2.Text
For Each xFN3 In xDRg3.Cells
xSV3 = xFN3.Text
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
Set xRg = xRg.Offset(1, 0)
Next
Next
End If
Next
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/366815.html
上一篇：搜索相關標題并排列在旁邊的表格中