vba - excel，从excel表中的一个单元格中提取时间中断？

Question

我有一个如下所示的 Excel 工作表，我只需要三个“中断”时间，即使它意味着删除每个单元格中除了这三个中断之外的所有内容。

Function GetBreaksTime(txt As String)
    Dim i As Long
    Dim arr As Variant

    arr = Split(txt, "Break")
    If UBound(arr) > 0 Then
        ReDim startTimes(1 To UBound(arr)) As String
        For i = 1 To UBound(arr)
            startTimes(i) = WorksheetFunction.Trim(Replace(Split(arr(i), "-")(0), vbLf, ""))
        Next
        GetBreaksTime = startTimes
    End If  
End Function

这是我到目前为止得到的，但它不适用于每个单元格，并且它采用错误的值。

那么知道如何做到这一点吗？

标签： vbaexcelexcel-formula

Answer 1

如果您按vbLf中断时间拆分单元格值，则将始终遵循包含"Break".

以下应该有效：

Sub TestGetBreakTimes()
    Dim CellValue As String
    CellValue = Worksheets("Sheet1").Range("A1").Value

    Dim BreakTimes As Variant
    BreakTimes = GetBreakTimes(CellValue)

    Debug.Print Join(BreakTimes, vbLf)  'the join is just to output the array at once.

    'to output in different cells loop through the array
    Dim i As Long
    For i = 0 To UBound(BreakTimes)
        Cells(3 + i, "A") = BreakTimes(i)
    Next i

    'or for a even faster output use
    Range("A3").Resize(UBound(BreakTimes) + 1).Value = WorksheetFunction.Transpose(BreakTimes)
End Sub

Function GetBreakTimes(InputData As String) As Variant
    Dim BreakTimes() As Variant
    ReDim BreakTimes(0)

    Dim SplitArr As Variant
    SplitArr = Split(InputData, vbLf) 'split by line break

    If UBound(SplitArr) > 0 Then
        Dim i As Long
        For i = 0 To UBound(SplitArr)
            If SplitArr(i) = "Break" Then 'if line contains break then next line is the time of the break
                If BreakTimes(0) <> vbNullString Then ReDim Preserve BreakTimes(UBound(BreakTimes) + 1)
                BreakTimes(UBound(BreakTimes)) = SplitArr(i - 1) 'collect break time
            End If
        Next i
        GetBreakTimes = BreakTimes
    End If
End Function

要分析完整的范围，您必须遍历第 2 行

Sub GetAllBreakTimes()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Dim LastCol As Long
    LastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column

    Dim BreakTimes As Variant

    Dim iCol As Long
    For iCol = 1 To LastCol
        BreakTimes = GetBreakTimes(ws.Cells(2, iCol).Value)
        ws.Cells(3, iCol).Resize(UBound(BreakTimes) + 1).Value = WorksheetFunction.Transpose(BreakTimes)
    Next iCol
End Sub