如何在两个单元格的值之间划线

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

在所有行上执行代码循环，用“偶然性”标识行，并将那里的非空单元格值连接起来。

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

Answer 2

使用示例中的代码并对其进行操作以供您使用：

类：

With Worksheets(1).Range("c1:c10000") 'Change the worksheet & Range
     Set c = .Find("Contingency", lookin:=xlValues) 'change the lookup text 
     If Not c Is Nothing Then
        firstAddress = c.Address
        Do
             c.Value = 5 ' Put your concatenate code here instead of this line
            Set c = .FindNext(c)
        If c is Nothing Then
            GoTo DoneFinding
        End If
        Loop While c.Address <> firstAddress
      End If
      DoneFinding:
End With

摘自：Range.FindNext