filling a table based on header row name

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

Matt,
Please wrap your code.
And it would help if you explained what is wrong with that code.

The condition in the Do..Loop doesn't change with each iteration of the loop. myOffset needs to be incorperated into the test for the Do.

Also,myOffset needs to be reset to 0 for each new row, lest the Do Loop start with the myOffset value from the last row.

If the Offset(0,1) value is present on Row1, but the Offset(0,2) is not, myOffset will increase without limit. A test to make sure that it exits should be added.

This worked for me.

Dim c As Range, r As Range
Dim isDuty As Boolean, myOffset As Long
Dim Status As Variant

For Each c In Sheets("Staff Monday").Range("B2:B100")
    isDuty = IsNumeric(c.Value)
    If isDuty = False Then
        Status = c.Value
        With Range(c.Offset(0, 5), c.Offset(0, 148))
            If IsNumeric(Application.Match(c.Offset(0, 2).Value, .EntireColumn.Rows(1), 0)) Then
                For Each r In .Cells
                    If Cells(1, r.Column).Value = c.Offset(0, 1) Then
                        r.Value = Status
                        myOffset = 0
                        Do Until Cells(1, r.Column + myOffset).Value = c.Offset(0, 2).Value
                            r.Offset(0, myOffset).Value = Status
                            myOffset = myOffset + 1
                        Loop
                    End If
                Next r
            Else
                MsgBox "No stop value for row " & c.Row
            End If
        End With
    End If
Next c

You may want change the Do test to

Do Until Cells(1, r.Column + myOffset - 1).Value = c.Offset(0, 2).Value

in order to write to the column that matches the c.Offset(0,2).Value

I hope this helps.