Hi,

I need to check rows of data which hold the tasks performed at
different times throughout a working day. I want to extract the start
and end times of the meal breaks within each row. I can do this by
checking the value of each cell and taking the header value (the time)
of the column which contains the start of the break, but how do i then
continue to check the remaining part of the working day to find the
end of break time and importantly, the start and end of any other meal
breaks within the same row?

I would want to paste the time values found into the same row, at the
end of the dataset.
A point to note, duties can have none, one or two meal breaks.

Hope you can help?

Kind regards,
Matt

You need to post samples of the columns and rows in the worksheet. You
description is too vague to give an answer.

"MJKelly" wrote:


Hi,

I need to check rows of data which hold the tasks performed at
different times throughout a working day. I want to extract the start
and end times of the meal breaks within each row. I can do this by
checking the value of each cell and taking the header value (the time)
of the column which contains the start of the break, but how do i then
continue to check the remaining part of the working day to find the
end of break time and importantly, the start and end of any other meal
breaks within the same row?

I would want to paste the time values found into the same row, at the
end of the dataset.
A point to note, duties can have none, one or two meal breaks.

Hope you can help?

Kind regards,
Matt

The data

Name 06:00 06:10 06:20 06:30 .......... Start End Start
End
Joe XD MR MR XD 06:10 06:30

The code would need to identify the MR value (meal relief) and copy
the time in the header column to the Start column, then the end of the
MR time would go into the End column, the code would continue to look
for a second MR (hence the two instances of Start and End), and then
go to the next row and perform the same check.

Does this help?

kind regards,
Matt

Set c = Rows(1).Find(what:="Start", LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Start - Exiting Macro")
Exit Sub
End If
StartCol = c.Column

RowCount = 2
Do While Range("A" & RowCount) < ""
TimeCol = StartCol
Set c = Rows(RowCount).Find(what:="MR", LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
StartAddress = c.Address
Do
Cells(RowCount, TimeCol) = Cells(1, c.Column)
Cells(RowCount, TimeCol).NumberFormat = "HH:MM"
TimeCol = TimeCol + 1
Set c = Rows(RowCount).FindNext(after:=c)
Loop While c.Address < StartAddress
End If
RowCount = RowCount + 1
Loop

"MJKelly" wrote:


The data

Name 06:00 06:10 06:20 06:30 .......... Start End Start
End
Joe XD MR MR XD 06:10 06:30

The code would need to identify the MR value (meal relief) and copy
the time in the header column to the Start column, then the end of the
MR time would go into the End column, the code would continue to look
for a second MR (hence the two instances of Start and End), and then
go to the next row and perform the same check.

Does this help?

kind regards,
Matt

Thanks Joel, that excellent, however, the code is capturing all
instances of the MR status, instead of capturing the start time and
end time of each MR time duration. There will only be up to two MR
timewindows in a row, but it could be none, one or two.

To make it more complicated, the end of the MR time window needs to be
recorded as the column next to the end of the MR. ie a MR of 07:00 to
07:30 will be represented in the data table as below.

07:00 07:10 07:20 07:30
MR MR MR Work


I have tried to do this myself and have written the code below - But,
it is not working, and strangly, if I change the MR times in a record
the code ignores the changed data and outputs the original MR times
(I'm pulling my hair on this one).

Kind regards,
Matt

Sub Find_MR_Times()
'finds the MR start and end times (two different MR's can be
identified)

Dim a As Range
Dim b As Range
Dim c As Range
Dim d As Range
Dim e As Range

Dim MR1Start As Variant
Dim MR1End As Variant
Dim MR2Start As Variant
Dim MR2End As Variant

MR1Start = ""
MR1End = ""
MR2Start = ""
MR2End = ""

Dim MR1StartCol As Integer
Dim MR1Endcol As Integer
Dim MR2StartCol As Integer

MR1StartCol = 0
MR1Endcol = 0
MR2StartCol = 0

For Each a In ThisWorkbook.Sheets("Monday").Range("B4:B20")
If Not a.Value = "" Then
For Each b In
ThisWorkbook.Sheets("Monday").Range(a.Cells(a.Row, 7), a.Cells(a.Row,
150))
If b.Value = "MR" Then
MR1Start = Cells(3, b.Column).Value
a.Offset(0, 152).Value = MR1Start
MR1StartCol = b.Column
Exit For
End If
Next b

If Not MR1Start = "" Then
For Each c In Range(a.Cells(a.Row, MR1StartCol),
a.Cells(a.Row, 150))
If Not c.Value = "MR" Then
MR1End = Cells(3, c.Column).Value
a.Offset(0, 153).Value = MR1End
MR1Endcol = c.Column
Exit For
End If
Next c

If Not MR1Start = "" Then
For Each d In Range(a.Cells(a.Row, MR1Endcol),
a.Cells(a.Row, 150))
If d.Value = "MR" Then
MR2Start = Cells(3, d.Column).Value
a.Offset(0, 154).Value = MR2Start
MR2StartCol = d.Column
Exit For
End If
Next d

If Not MR2Start = "" Then
For Each e In Range(a.Cells(a.Row, MR2StartCol),
a.Cells(a.Row, 150))
If Not e.Value = "MR" Then
MR2End = Cells(3, e.Column).Value
a.Offset(0, 155).Value = MR2End
Exit For

End If
Next e

Else
a.Offset(0, 154).Value = "~"

End If

Else
a.Offset(0, 153).Value = "~"
End If


End If
End If

Next a

MR1Start = ""
MR1End = ""
MR2Start = ""
MR2End = ""

MR1StartCol = 0
MR1Endcol = 0
MR2StartCol = 0

End Sub

Try this code. I use a STATE variable FOUND to determine when the 1st MR is
found and then set it FALSE when there isn't an MR. The code may need to be
modified slightly for the last time column.

If found = True Then
Cells(RowCount, TimeCol) = Cells(1, ColCount - 1)
Cells(RowCount, TimeCol).NumberFormat = "HH:MM"
End If

the section of code above is to cover the case where MR is in the last Time
column. I don't know if the First "Start" is in the column after the last
Time period or there are some columns inbetween like

Col X Col Y
11:40 PM Start
MR
or

Col X Col Y Col Z
11:40 PM Start
MR

Here is the modified code.

Set c = Rows(1).Find(what:="Start", LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Start - Exiting Macro")
Exit Sub
End If
StartCol = c.Column

RowCount = 2
Do While Range("A" & RowCount) < ""
TimeCol = StartCol
ColCount = 2
found = False
Do While ColCount < StartCol
Select Case found
Case True:
If Cells(RowCount, ColCount) < "MR" Then
Cells(RowCount, TimeCol) = Cells(1, ColCount - 1)
Cells(RowCount, TimeCol).NumberFormat = "HH:MM"
TimeCol = TimeCol + 1
found = False
End If
Case False:
If Cells(RowCount, ColCount) = "MR" Then
Cells(RowCount, TimeCol) = Cells(1, ColCount)
Cells(RowCount, TimeCol).NumberFormat = "HH:MM"
TimeCol = TimeCol + 1
found = True
End If
End Select
ColCount = ColCount + 1
Loop
If found = True Then
Cells(RowCount, TimeCol) = Cells(1, ColCount - 1)
Cells(RowCount, TimeCol).NumberFormat = "HH:MM"
End If
RowCount = RowCount + 1
Loop


"MJKelly" wrote:

Thanks Joel, that excellent, however, the code is capturing all
instances of the MR status, instead of capturing the start time and
end time of each MR time duration. There will only be up to two MR
timewindows in a row, but it could be none, one or two.

To make it more complicated, the end of the MR time window needs to be
recorded as the column next to the end of the MR. ie a MR of 07:00 to
07:30 will be represented in the data table as below.

07:00 07:10 07:20 07:30
MR MR MR Work


I have tried to do this myself and have written the code below - But,
it is not working, and strangly, if I change the MR times in a record
the code ignores the changed data and outputs the original MR times
(I'm pulling my hair on this one).

Kind regards,
Matt

Sub Find_MR_Times()
'finds the MR start and end times (two different MR's can be
identified)

Dim a As Range
Dim b As Range
Dim c As Range
Dim d As Range
Dim e As Range

Dim MR1Start As Variant
Dim MR1End As Variant
Dim MR2Start As Variant
Dim MR2End As Variant

MR1Start = ""
MR1End = ""
MR2Start = ""
MR2End = ""

Dim MR1StartCol As Integer
Dim MR1Endcol As Integer
Dim MR2StartCol As Integer

MR1StartCol = 0
MR1Endcol = 0
MR2StartCol = 0

For Each a In ThisWorkbook.Sheets("Monday").Range("B4:B20")
If Not a.Value = "" Then
For Each b In
ThisWorkbook.Sheets("Monday").Range(a.Cells(a.Row, 7), a.Cells(a.Row,
150))
If b.Value = "MR" Then
MR1Start = Cells(3, b.Column).Value
a.Offset(0, 152).Value = MR1Start
MR1StartCol = b.Column
Exit For
End If
Next b

If Not MR1Start = "" Then
For Each c In Range(a.Cells(a.Row, MR1StartCol),
a.Cells(a.Row, 150))
If Not c.Value = "MR" Then
MR1End = Cells(3, c.Column).Value
a.Offset(0, 153).Value = MR1End
MR1Endcol = c.Column
Exit For
End If
Next c

If Not MR1Start = "" Then
For Each d In Range(a.Cells(a.Row, MR1Endcol),
a.Cells(a.Row, 150))
If d.Value = "MR" Then
MR2Start = Cells(3, d.Column).Value
a.Offset(0, 154).Value = MR2Start
MR2StartCol = d.Column
Exit For
End If
Next d

If Not MR2Start = "" Then
For Each e In Range(a.Cells(a.Row, MR2StartCol),
a.Cells(a.Row, 150))
If Not e.Value = "MR" Then
MR2End = Cells(3, e.Column).Value
a.Offset(0, 155).Value = MR2End
Exit For

End If
Next e

Else
a.Offset(0, 154).Value = "~"

End If

Else
a.Offset(0, 153).Value = "~"
End If


End If
End If

Next a

MR1Start = ""
MR1End = ""
MR2Start = ""
MR2End = ""

MR1StartCol = 0
MR1Endcol = 0
MR2StartCol = 0

End Sub