Cycle Time - Macro Help

[Plokimu77]

Hello Forum,

My code below works great, however I have multiple unique PIDLabel groups in column D, separated with/by an empty row,
however my current macro stops in the first grouping.

Can some one please help me, so it can provide me the cycle time for each of the groups in column D?

Thank you

Sub MCycleTime()

Dim LastRow As Long
Dim i As Long
Dim StartTime As Date
Dim EndTime As Date
Dim FoundFirst030 As Boolean
Dim OutputRow As Long


' Find the last row with data in column E
LastRow = Cells(Rows.Count, "E").End(xlUp).Row

' Initialize vari030les
FoundFirst030 = False

' Loop through the rows
For i = 2 To LastRow ' Assuming row 1 contains headers

' Check if the event code is 030
If Cells(i, "E").Value = "030" Then
StartTime = Cells(i, "N").Value
FoundFirst030 = True
End If

' Check if the event code is 100 and we've already found the first 030
If Cells(i, "E").Value = "100" And FoundFirst030 Then
EndTime = Cells(i, "N").Value
OutputRow = i
End If

Next i

' Calculate and display the cycle time
If FoundFirst030 And EndTime > 0 Then
Cells(OutputRow, "O").Value = Format(EndTime - StartTime, "hh:mm:ss")
Else

MsgBox "Cycle time measurement could not be completed."

End If

End Sub

 

[offthelip]

you are not resetting your control flags when you get tothe end of the cycle time and you had the loop control such that you only wrote out the results once, try this modification:

Sub MCycleTime()

Dim LastRow As Long
Dim i As Long
Dim StartTime As Date
Dim EndTime As Date
Dim FoundFirst030 As Boolean
Dim OutputRow As Long


' Find the last row with data in column E
LastRow = Cells(Rows.Count, "E").End(xlUp).Row

' Initialize vari030les
FoundFirst030 = False

' Loop through the rows
For i = 2 To LastRow ' Assuming row 1 contains headers

' Check if the event code is 030
If Cells(i, "E").Value = "030" And Not (FoundFirst030) Then
StartTime = Cells(i, "N").Value
FoundFirst030 = True
End If

' Check if the event code is 100 and we've already found the first 030
If Cells(i, "E").Value = "100" And FoundFirst030 Then
EndTime = Cells(i, "N").Value
OutputRow = i
End If


' Calculate and display the cycle time
If FoundFirst030 And EndTime > 0 Then
Cells(OutputRow, "O").Value = Format(EndTime - StartTime, "hh:mm:ss")
FoundFirst030 = False
Else

MsgBox "Cycle time measurement could not be completed."

End If
Next i
End Sub

 

[Plokimu77]

Thank you.

However, now the MsgBox keeps appearing and I have to keep on clicking ok.

Is there something you can modify to help?

 

[offthelip]

It really depends on what your data includes: in each group of data is there always an "endtime" ?? if there is you can just delete that line. If there isnt you need to have another way of detecting that you have got to the end of the group, ( i.e. is there a blank cell in column D??)

 

[Plokimu77]

Actually each grouping is separated by an empty row.

Here is an example of my data.

TRAC	ID	MACODE	PIDLABEL	EVENTCODE	DATE	TIME	ZIPCODE	DEVICE	DEVICEID	ZIPCODE	KCODE	FACID	TIMESTAMP
1,000,000	420	901	100701832876206	030	9/29/2023	16:42:48	76179	MI	15116D81DB	76179	801	698,322	9/29/2023 16:42:48.000
1,000,002	420	901	100701832876206	PL	9/29/2023	19:56:38		MH	?	?	801	-1	9/29/2023 19:56:38.000
-1,001	420	901	100701832876206	100	9/29/2023	19:56:38	76161	SP	001-000002	?	801	37,628	9/29/2023 19:56:38.000
1,000,000	420	901	100702617054161	030	9/20/2023	10:43:18	76106	MD	C102A17867	76106	801	78,504	9/20/2023 10:43:18.000
1,000,001	420	901	100702617054161	030	9/28/2023	05:52:25	76106	MI	14231D8753	76106	801	78,504	9/28/2023 05:52:25.000
1,000,002	420	901	100702617054161	16	9/28/2023	05:57:33	76106	MI	14231D8753	76106	801	78,504	9/28/2023 05:57:33.000
1,000,005	420	901	100702617054161	PL	9/29/2023	07:30:03		MH	?	?	801	-1	9/29/2023 07:30:03.000
-1,001	420	901	100702617054161	100	9/29/2023	07:30:03	76161	SV	S018702280	?	801	37,640	9/29/2023 07:30:03.000
1,000,000	420	901	100702639023336	030	10/6/2023	06:35:22	76106	MI	14231D8753	76106	801	78,504	10/6/2023 06:35:22.000
1,000,001	420	901	100702639023336	16	10/6/2023	06:37:40	76106	MI	14231D8753	76106	801	78,504	10/6/2023 06:37:40.000
1,000,004	420	901	100702639023336	PL	10/6/2023	21:39:36		MH	?	?	801	-1	10/6/2023 21:39:36.000
-1,001	420	901	100702639023336	100	10/6/2023	21:39:36	76161	SP	001-000003	?	801	37,628	10/6/2023 21:39:36.000
1,000,000	420	901	100702639243925	030	9/22/2023	10:16:37	76240	MD	D051A00919	76240	801	37,668	9/22/2023 10:16:37.000
1,000,002	420	901	100702639243925	PL	9/22/2023	19:46:16		MH	?	?	801	-1	9/22/2023 19:46:16.000
-1,001	420	901	100702639243925	100	9/22/2023	19:46:16	76161	SP	001-000002	?	801	37,628	9/22/2023 19:46:16.000
1,000,000	420	901	100702647814544	030	9/15/2023	13:24:24	76179	MD	C063A00931	76106	801	78,504	9/15/2023 13:24:24.000
1,000,001	420	901	100702647814544	030	9/16/2023	11:22:00	76106	MI	15084D8571	76106	801	78,504	9/16/2023 11:22:00.000
1,000,002	420	901	100702647814544	16	9/16/2023	11:33:23	76106	MI	15084D8571	76106	801	78,504	9/16/2023 11:33:23.000
1,000,005	420	901	100702647814544	PL	9/29/2023	07:27:50		MH	?	?	801	-1	9/29/2023 07:27:50.000
-1,001	420	901	100702647814544	100	9/29/2023	07:27:50	76161	SV	S018702280	?	801	37,640	9/29/2023 07:27:50.000
1,000,000	420	901	100702650550514	030	9/29/2023	11:44:23	76119	PO	?	76119	801	52,997	9/29/2023 11:44:23.000
-1,002	420	901	100702650550514	100	9/29/2023	22:22:03	76161	AP	022-1	?	801	37,628	9/29/2023 22:22:03.000
-1,001	420	901	100702650550514	100	9/29/2023	22:27:13	76161	AP	022-1	?	801	37,628	9/29/2023 22:27:13.000
1,000,002	420	901	100702650550514	PL	9/29/2023	22:27:13		MH	?	?	801	-1	9/29/2023 22:27:13.000
1,000,000	420	901	100702650834065	030	10/5/2023	13:13:05	76131	MD	C061A01448	76106	801	78,504	10/5/2023 13:13:05.000
1,000,001	420	901	100702650834065	030	10/5/2023	13:13:15	76131	MD	C061A01448	76106	801	78,504	10/5/2023 13:13:15.000
1,000,002	420	901	100702650834065	030	10/6/2023	06:35:07	76106	MI	14231D8753	76106	801	78,504	10/6/2023 06:35:07.000
1,000,003	420	901	100702650834065	16	10/6/2023	06:39:25	76106	MI	14231D8753	76106	801	78,504	10/6/2023 06:39:25.000
1,000,006	420	901	100702650834065	PL	10/6/2023	21:41:08		MH	?	?	801	-1	10/6/2023 21:41:08.000
-1,001	420	901	100702650834065	100	10/6/2023	21:41:08	76161	SP	001-000004	?	801	37,628	10/6/2023 21:41:08.000

 

[offthelip]

try this modification which detects the blank cell in column E and resets the logic

Sub MCycleTime()

Dim LastRow As Long
Dim i As Long
Dim StartTime As Date
Dim EndTime As Date
Dim FoundFirst030 As Boolean
Dim OutputRow As Long


' Find the last row with data in column E
LastRow = Cells(Rows.Count, "E").End(xlUp).Row

' Initialize vari030les
FoundFirst030 = False

' Loop through the rows
For i = 2 To LastRow ' Assuming row 1 contains headers

' Check if the event code is 030
If Cells(i, "E").Value = "030" And Not (FoundFirst030) Then
StartTime = Cells(i, "N").Value
FoundFirst030 = True
End If

' Check if the event code is 100 and we've already found the first 030
If Cells(i, "E").Value = "100" And FoundFirst030 Then
EndTime = Cells(i, "N").Value
OutputRow = i
End If


' Calculate and display the cycle time
If FoundFirst030 And EndTime > 0 Then
Cells(OutputRow, "O").Value = Format(EndTime - StartTime, "hh:mm:ss")
FoundFirst030 = False
End If

' reset for next group when endtime not found
If FoundFirst030 And Cells(i, "E").Value = "" Then
FoundFirst030 = False
End If

Next i
End Sub