excel - 如何在两个单元格的值之间换行

Question

我正在尝试根据两个单元格的值来完成换行公式。更具体地说，CONTINGENCY和 END .所以一个例子是这样的:
`

应急“XXX”

断开总线 1

断开总线 2

断开总线 3

断开总线 4

结尾
-------------------- `

我想要的输出是这样的
CONTINGENCY 'XXX'
DISCONNECT BUS 1
DISCONNECT BUS 2
DISCONNECT BUS 3
DISCONNECT BUS 4
END
它被合并到 contingency 之间的一个单元格中和 end .



我也必须为 10000 行代码做这件事。我应该输入一个标识符或其他东西，比如 contingency 的唯一 ID 号?接受任何建议或解决方案。

所以我使用换行发短信并使用 CHAR (10) 进行换行，但无法找出特定单元格值或标识符的代码。
=C2&Q&C3&Q&C4&Q&C5&Q&C6&Q&C7在哪里 Q=CHAR(10)

Answer 1

下面的代码循环遍历所有行，用“CONTINGENCY”标识行并连接那里的非空单元格值。

Private Sub ConcatCells()
    Dim ws As Worksheet
    Dim currentRow As Long, concatRow As Long, lastRow As Long
    Dim WithinBlock As Boolean

    Set ws = ActiveWorkbook.Worksheets("Whatever")

    With ws
        lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        For currentRow = 1 To lastRow

            If Left(.Cells(currentRow, "C").Value, 11) = "CONTINGENCY" Then
                WithinBlock = True
                concatRow = currentRow
                .Cells(concatRow, "D").Value = .Cells(currentRow, "C").Value

            ElseIf Left(.Cells(currentRow, "C").Value, 3) = "END" Then
                WithinBlock = False
                .Cells(concatRow, "D").Value = _
                    .Cells(concatRow, "D").Value & vbLf & _
                    .Cells(currentRow, "C").Value

            ElseIf WithinBlock And .Cells(currentRow, "C").Value <> "" Then
                .Cells(concatRow, "D").Value = _
                    .Cells(concatRow, "D").Value & vbLf & _
                    .Cells(currentRow, "C").Value

            End If
        Next currentRow
    End With
End Sub