Hi,

Can you help me with the code below?
I'm trying to fill a status taken from column b and put it in a table
in between two time values. the first time value is c.offset(0,1) and
the second is c.offset(0,2). In the table the times are held in row
1. The status will be sick or holiday etc, if the person is at work
they have a numeric duty code hence the isnumeric check. Once the
status has been filled to the time window, the next "c" in the range
is checked.

For Each c In Sheets("Staff Monday").Range("B2:B100")
isDuty = IsNumeric(c.Value)
If isDuty = False Then
Status = c.Value
For Each r In Range(c.Offset(0, 5), c.Offset(0, 148))
If Cells(1, r.Column).Value = c.Offset(0, 1) Then
r.Value = Status

Do Until Cells(1, r.Column).Value = c.Offset(0, 2).Value
r.Offset(0, MyOffset).Value = Status
MyOffset = MyOffset + 1
Loop
End If


Next r
End If
Next c

regards,
Matt

It would help if you'd tell us what was wrong with the original answer
instead of reposting the question again.

"MJKelly" wrote:

Hi,

Can you help me with the code below?
I'm trying to fill a status taken from column b and put it in a table
in between two time values. the first time value is c.offset(0,1) and
the second is c.offset(0,2). In the table the times are held in row
1. The status will be sick or holiday etc, if the person is at work
they have a numeric duty code hence the isnumeric check. Once the
status has been filled to the time window, the next "c" in the range
is checked.

For Each c In Sheets("Staff Monday").Range("B2:B100")
isDuty = IsNumeric(c.Value)
If isDuty = False Then
Status = c.Value
For Each r In Range(c.Offset(0, 5), c.Offset(0, 148))
If Cells(1, r.Column).Value = c.Offset(0, 1) Then
r.Value = Status

Do Until Cells(1, r.Column).Value = c.Offset(0, 2).Value
r.Offset(0, MyOffset).Value = Status
MyOffset = MyOffset + 1
Loop
End If


Next r
End If
Next c

regards,
Matt