Reading Text file and splitting dynamic sized string into array help

[BalloutMoe]

Hello all I have the following in a text file that is being read by vba and going line by line

60ANDREW COX 0.00A08:00 09:58 ANDREW COX
60DAVID BEAUMONT 0.00A08:08 13:26 14:09 18:00 D. BEAUMONT
60JOSEPG CAMACHO 0.00A08:04 11:10 11:41 18:01 JO CAMACHO
60JUAN CABRERA 0.00A08:00 18:02 JUAN CABRERA
60ROBERT ELMORE 0.00A08:04 18:02 ROBERT ELMOR
60RUBEN CABRERA 0.00A15:00 18:02 RUBEN CABRER
60TRISTON HILL 0.00A08:02 11:52 12:27 18:02 TRISTON HILL
60JOSE VARGAS 0.00A08:19 16:22 16:54 18:01 VARGAS
99 END OF DAY

I would like to split the strings for example

David Beamount	08:08	13:26
David Beamount	14:09	18:00
Josepg Camacho	08:04	11:10
Josepg Camacho	11:41	18:01

and So on for each employee. Sometime the row can have a bunch of different time clocks. Each two represent a clock in and clock out. How can I split it dynamically and enter it into a sheet? I have the following code below it works but only first the first clock and clock out. Thank you for your help

  For i = 0 To UBound(arrTxt)
  
      If InStr(arrTxt(i), todaysdate2) > 0 Then
         Do While InStr(arrTxt(i + j), "END OF DAY") = 0
            
            j = j + 1
            
            For t = 2 To 17
                                   If InStr(arrTxt(i + j), "60" & ThisWorkbook.Worksheets("Times").Range("K" & t) & " " & ThisWorkbook.Worksheets("Times").Range("L" & t)) > 0 Then  'Safety Inspection
                                    'SI = InStr(arrTxt(i + j), "60ANDREW ")
                                    arrTime = Split(WorksheetFunction.Trim(arrTxt(i + j)), " ")
                                    
                                    arrTime(0) = Right(arrTime(0), Len(arrTime(0)) - 2) & " " & arrTime(1)
                                    arrTime(2) = Split(arrTime(2), "A")(1)
                                    
                                            If arrTime(2) <= "08:10" Then
                                            arrTime(2) = "08:00"
                                            End If
                                     
                                            If arrTime(3) >= "17:53" And arrTime(3) <= "18:25" Then
                                            arrTime(3) = "18:00"
                                            End If
                          
                
                                            lastR2 = ThisWorkbook.Worksheets("Times").Range("A" & Sh.Rows.Count).End(xlUp).Row
                                            On Error Resume Next
                                            ThisWorkbook.Worksheets("Times").Range("A" & lastR2 + 1).Resize(1, 1).Value = arrTime(0)
                                            ThisWorkbook.Worksheets("Times").Range("B" & lastR2 + 1).Resize(1, 1).Value = arrTime(2)
                                            ThisWorkbook.Worksheets("Times").Range("C" & lastR2 + 1).Resize(1, 1).Value = arrTime(3)
                                            ThisWorkbook.Worksheets("Times").Range("D" & lastR2 + 1).Resize(1, 1).Value = todaysdate
                                            
                                  
                                   End If
                 Next
                                
         Loop 'For Do

      End If 'If Instr
  Next i

 

[BalloutMoe]

How can I make my code dynamic instead of arrTime(0) and so on

 

[johnnyL]

Are you going to be altering any additional times after the the first two?

 

[BalloutMoe]

Are you going to be altering any additional times after the the first two?

the last one yes

 

[johnnyL]

So if there were 8 clock times for one person in a line of text, you will alter the first 2 clock times as well as the last 2 clock times?

 

[BalloutMoe]

So if there were 8 clock times for one person in a line of text, you will alter the first 2 clock times as well as the last 2 clock times?

Correct

 

[johnnyL]

You want 'todaysdate' at the end of each line displayed?

 

[BalloutMoe]

You want 'todaysdate' at the end of each line displayed?

yes please

 

[johnnyL]

Sorry, I got side tracked with grandchild and dinner.

I haven't tested any of this code, just wrote it out in my head. Try it out and let me know how it goes. If it does not go well, perhaps you could include some more code that shows how exactly you arrive at the point of code that you did post.

Anyways, try replacing the code you posted in post #1 with:

    Dim arrTimeSlot         As Long
    Dim ClockTimesProcessed As Long, TimeClocksFound    As Long
    Dim OutputArrayRow      As Long
    Dim OutputArray         As Variant
'
    ReDim OutputArray(1 To 4, 1 To 10000)
    arrTimeSlot = 0
    ClockTimesProcessed = 0                                                                                         ' Initiate ClockTimesProcessed
    OutputArrayRow = 0
'
    For i = 0 To UBound(arrTxt)
        If InStr(arrTxt(i), todaysdate2) > 0 Then
            Do While InStr(arrTxt(i + j), "END OF DAY") = 0
                j = j + 1
'
                For t = 2 To 17
                    If InStr(arrTxt(i + j), "60" & ThisWorkbook.Worksheets("Times").Range("K" & t) & " " & _
                            ThisWorkbook.Worksheets("Times").Range("L" & t)) > 0 Then  'Safety Inspection
'                       SI = InStr(arrTxt(i + j), "60ANDREW ")
                        OutputArrayRow = OutputArrayRow + 1                                                     ' Increment OutputArrayRow
                        arrTime = Split(WorksheetFunction.Trim(arrTxt(i + j)), " ")                             ' Get Data Line into arrTime
'
                        TimeClocksFound = UBound(arrTime) - 3                                                   ' Calculate the # of TimeClocksFound
'
                        arrTime(0) = Right(arrTime(0), Len(arrTime(0)) - 2) & " " & arrTime(1)                  ' Get the First & Last Name from beginning
                        OutputArray(1, OutputArrayRow) = arrTime(0)                                             ' Save the Employee Name into OutputArray
'
                        arrTime(2) = Split(arrTime(2), "A")(1)                                                  ' Get the first Time
                        If arrTime(2) <= "08:10" Then arrTime(2) = "08:00"                                      ' Adjust the first Time if needed
                        ClockTimesProcessed = ClockTimesProcessed + 1                                           ' Increment ClockTimesProcessed
                        OutputArray(2, OutputArrayRow) = arrTime(2)                                             ' Save the first clock in time into OutputArray
'
                        If arrTime(3) >= "17:53" And arrTime(3) <= "18:25" Then arrTime(3) = "18:00"            ' Adjust the second Time if needed
                        ClockTimesProcessed = ClockTimesProcessed + 1                                           ' Increment ClockTimesProcessed
                        OutputArray(3, OutputArrayRow) = arrTime(3)                                             ' Save the first clock out time into OutputArray
'
                        OutputArray(4, OutputArrayRow) = todaysdate                                             ' Save todaysdate into OutputArray
'
'---------------------------------------------------------------------------------------------------------------
'
                        If ClockTimesProcessed = TimeClocksFound Then GoTo NextLineOfData                       ' If no more TimeClocks then goto next Data
'
                        arrTimeSlot = 3                                                                         ' Initialize arrTimeSlot
'

                        Do While ClockTimesProcessed < TimeClocksFound
                            arrTimeSlot = arrTimeSlot + 1                                                       ' Increment arrTimeSlot
                            OutputArrayRow = OutputArrayRow + 1                                                 ' Increment OutputArrayRow
'
                            OutputArray(1, OutputArrayRow) = arrTime(0)                                         ' Save the Employee Name into OutputArray
'
                            If TimeClocksFound - ClockTimesProcessed = 2 Then                                   ' If last set of TimeClocks found then ...
                                If arrTime(arrTimeSlot) <= "08:10" Then arrTime(arrTimeSlot) = "08:00"          ' Adjust the clock in Time if needed
                                OutputArray(2, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock in time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                arrTimeSlot = arrTimeSlot + 1                                                   ' Increment arrTimeSlot
                                If arrTime(arrTimeSlot) >= "17:53" And arrTime(arrTimeSlot) <= "18:25" Then
                                    arrTime(arrTimeSlot) = "18:00"                                              ' Adjust the clock out Time if needed
                                End If
'
                                OutputArray(3, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock out time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                OutputArray(4, OutputArrayRow) = todaysdate                                     ' Save todaysdate into OutputArray
                            Else
                                OutputArray(2, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock in time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                arrTimeSlot = arrTimeSlot + 1                                                   ' Increment arrTimeSlot
                                OutputArray(3, OutputArrayRow) = arrTime(arrTimeSlot)                           ' Save the clock out time into OutputArray
                                ClockTimesProcessed = ClockTimesProcessed + 1                                   ' Increment ClockTimesProcessed
'
                                OutputArray(4, OutputArrayRow) = todaysdate                                     ' Save todaysdate into OutputArray
                            End If
                        Loop                                                                                    ' Loop back for next ClockTime
                    End If
NextLineOfData:
                Next
            Loop 'For Do
        End If 'If Instr
    Next i
'
    lastR2 = ThisWorkbook.Worksheets("Times").Range("A" & Sh.Rows.Count).End(xlUp).Row
    ReDim Preserve OutputArray(1 To 4, 1 To OutputArrayRow)
'
    ThisWorkbook.Worksheets("Times").Range("A" & lastR2 + 1).Resize(UBound(OutputArray, 2), UBound(OutputArray, 1)) = Application.Transpose(OutputArray)
End Sub

 

[BSALV]

Sub splitten()
     Dim Result(), t_IN, t_OUT
     
     a = Sheets("blad3").Range("a1").CurrentRegion.Value        'read your data to an array (or another method to get those data into an array)
     ReDim Result(1 To UBound(a) * 3, 1 To 4)                   'prepare result array with (precaution) 3 timeslots per employee

     For i = 1 To UBound(a)                                     'loop through the data
          sp = Split(a(i, 1), "0.00A")                          'split on this string
          If UBound(sp) = 1 Then                                'there are 2 parts
               sp1 = Split(sp(1))                               'split the 2nd part on the spaces
               For j = 0 To UBound(sp1) Step 2                  'loop through the times (per 2, in and out)
                    If InStr(sp1(j), ":") = 0 Then Exit For     'if there is no ":" in that part, then it is no longer a time, so quit
                    ptr = ptr + 1                               'increment pointer
                    Result(ptr, 1) = WorksheetFunction.Proper(Mid(sp(0), 3))     'Name

                    t_IN = TimeValue(sp1(j))                    'actual in
                    If t_IN <= TimeValue("08:10") Then t_IN = TimeValue("08:00")     'manipulate in
                    Result(ptr, 2) = t_IN                       'add to array

                    t_OUT = TimeValue(sp1(j + 1))               'actual out
                    If WorksheetFunction.Median(TimeValue("17:53"), TimeValue("18:25"), t_OUT) = t_OUT Then t_OUT = TimeValue("18:00")     'manipulate out
                    Result(ptr, 3) = t_OUT                      'add to array
               Next
          End If
     Next

     ThisWorkbook.Worksheets("Times").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(ptr, 3).Value = Result

End Sub