在Excel中使用Excel合并单元格文本

ewm0tg9j  于 2023-10-21  发布在  其他
关注(0)|答案(2)|浏览(146)

我有个问题问题是我需要将单元格中的文本合并到另一个单元格中。它应该看起来像这样:第一列中每四个单元格的文本应该连接起来(最好是用空格),并写入第二列中的每个单元格。我无法手动执行此操作,因为数据量太大。有没有人有代码,最好是JavaScript,可以做到这一点?
我有这个代码,但它不工作。

Sub vba_concatenate()
    Dim rng As Range
    Dim i As Long
    Dim SourceRange As Range
    Dim TargetRange As Range
    
    Set SourceRange = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    Set TargetRange = Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
    
    i = 1
    
    For Each rng In SourceRange
        TargetRange.Cells(i).Value = rng.Value
        i = i + 1
        
        If i > TargetRange.Cells.Count Then
            Exit For
        End If
    Next rng
End Sub
rxztt3cl

rxztt3cl1#

请注意,这可以单独使用公式来实现:

=TEXTJOIN(" ",TRUE,INDEX(A:A,MROUND(ROW()+1,4)-3):INDEX(A:A,MROUND(ROW()+1,4)))

对你的问题的另一种解释:

=TEXTJOIN(" ",TRUE,INDEX(A:A,ROW()*4-3):INDEX(A:A,ROW()*4))

3lxsmp7m

3lxsmp7m2#

试试看吧

Option Explicit
Sub Demo()
    Dim i As Long, j As Long, sTxt As String
    Dim arrData, arrRes(), k As Integer
    Dim rngData As Range
    ' Load data from Column A
    Set rngData = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    arrData = rngData.Value
    ReDim arrRes(1 To UBound(arrData), 1 To 1)
    k = 1
    ' Loop through data
    For i = LBound(arrData) To UBound(arrData) Step 4
        ' Cocate 4 cells
        For j = 0 To 3
            If i + j > UBound(arrData) Then Exit For
            sTxt = sTxt & " " & arrData(i + j, 1)
        Next j
        If Len(sTxt) Then
            arrRes(k, 1) = Mid(sTxt, 2)
            k = k + 1
        End If
        sTxt = ""
    Next i
    ' Write data to sheet
    rngData.Offset(0, 1).Value = arrRes
End Sub

相关问题