excel - VBA 连接两个范围

Question

我正在尝试使用 VBA 连接 excel 中的两个范围，因此 range1 中的每个单元格连接 range2 中的所有单元格，直到单元格 A 为空。请看下面：

Range1(column A): Range2(column B):
50703, 50702      52797, 52848

Concatenate(column C):
50703-52797, 50703-52848, 50702-52797, 50702-52848

标签： excelvba

Answer 1

这会将 A 列和 B 列中的所有值组合插入C 列，并用连字符连接它们：

Sub combinations()

    Dim i As Long, j As Long, n As Long
    Dim valsColA As Variant, valsColB As Variant

    With ThisWorkbook.Sheets("Combinations") ' change sheet name, if necessary

        valsColA = .Range(.Cells(1, 1), .Range("A1").End(xlDown)).Value
        valsColB = .Range(.Cells(1, 2), .Range("B1").End(xlDown)).Value

        For i = LBound(valsColA) To UBound(valsColA)
            For j = LBound(valsColB) To UBound(valsColB)
                n = n + 1
                .Cells(n, 3).Value = valsColA(i, 1) & "-" & valsColB(j, 1)
            Next j
        Next i

    End With
End Sub