vba - 连接多列

Question

我有一张表，列出了从 C12 开始的多个值，其中前六个数字相同，后三个数字不同，并用空格分隔(例如 123456 111 222 333 444)。我想使用 VBA 将单元格中的前六位数字与该单元格中的许多三位数值(例如 123456111、123456222、123456333、123456444)组合起来。这些组合值应分别位于各自的单元格中。每个单元格中三位数的数量各不相同。我想也许最简单的方法是将它们分成几列，然后将它们组合起来，但我无法弄清楚这一点。

这是一个例子:

#DATA#
1. 541259 139 285
2. 452679 245
3. 894623 455 654

#DESIRED RESULT#
1. 541259139
2. 541259285
3. 452679245
4. 894623455
5. 894623654

我能够使用以下代码将这些值分离到第二张纸上:

Sub Split()
Dim totalRows As Long, i As Long, sColNames As String
totalRows = LastRowWithData(Sheet1, "C")
For i = 1 To totalRows
    sColNames = Sheet1.Range("C" & i).value
    Call SplitToColumns(sColNames, " ", Sheet2.Range("A" & i))
Next i
End Sub

我不确定这是否是最好的方法，而且我不知道如何加入他们。

感谢您的宝贵时间!

Answer 1

这是我的做法:

Sub SplitMyNum()
    Dim i&, j&
    Dim splt() As String
    Dim rngArr() As Variant
    Dim oWs As Worksheet
    Dim tWs As Worksheet

    Set oWs = Worksheets("Sheet1") 'change to your input
    Set tWs = Worksheets("Sheet2") 'change to your output sheet, may be the same as above

    With oWs
        rngArr = .Range(.Cells(1, 3), .Cells(.Rows.Count, 3).End(xlUp)).Value 'loads all the numbers into an array
    End With
    With tWs
        For i = LBound(rngArr, 1) To UBound(rngArr, 1) 'iterate through the numbers
            splt = Split(rngArr(i, 1), " ") 'Split the numbers on spaces
            For j = LBound(splt) + 1 To UBound(splt) 'iterates through the split values
                'Next line concatenates the first with each set after and puts it in the next available cell in column A.
                .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Value = splt(LBound(splt)) & splt(j)
            Next j
        Next i
    End With
End Sub