Hi,

The code below is attempting to loop through a range of tasks (in a
single row) and once a task is found, offset a given number (negative)
of rows to capture the start time (which is in the header row) and
paste this value into another column on the origianal row. Then I
need the next row queried (the next job/person).

I just can't get it right, please help?

Sub AddStartTimes()

Dim R As Range
Dim ColOffset As Integer
Dim RowOffset As Integer
Dim C As Range

RowOffset = 1
ColOffset = 0


ThisWorkbook.Sheets("Monday").Select
For Each R In Range("D4:D1000")
For Each C In Range("H4").Offset(RowOffset,
0).Range("EU4").Offset(RowOffset, 0)
If Not C.Value = "" Then
R.Value = C.Offset(0 - RowOffset, 0).Value
Exit For
Else
ColOffset = ColOffset + 1
End If
Next C

RowOffset = RowOffset + 1
Next R


End Sub

I may be missing something in your question, but why do you need the
offset... the header row is Row 1, and it will always be Row 1, no matter
what row of data you are currently working on, right?

--
Rick (MVP - Excel)


"MJKelly" wrote in message
...


Hi,

The code below is attempting to loop through a range of tasks (in a
single row) and once a task is found, offset a given number (negative)
of rows to capture the start time (which is in the header row) and
paste this value into another column on the origianal row. Then I
need the next row queried (the next job/person).

I just can't get it right, please help?

Sub AddStartTimes()

Dim R As Range
Dim ColOffset As Integer
Dim RowOffset As Integer
Dim C As Range

RowOffset = 1
ColOffset = 0


ThisWorkbook.Sheets("Monday").Select
For Each R In Range("D4:D1000")
For Each C In Range("H4").Offset(RowOffset,
0).Range("EU4").Offset(RowOffset, 0)
If Not C.Value = "" Then
R.Value = C.Offset(0 - RowOffset, 0).Value
Exit For
Else
ColOffset = ColOffset + 1
End If
Next C

RowOffset = RowOffset + 1
Next R


End Sub

Rick,
True the header row will always be row 1.

I'm still not sure how to write the code to reflect this however?

Matt

It is a little hard to figure out exactly what you were trying to do based
on the code you posted as that code seems to be iterating *down* a column
while your message appears to be asking for a changing *column* value. So,
without more information about what you really want to do, I can't suggest
actual code, but see if this code sample helps you. Put some values in F1
through M1 and some other values in F4 through M4. Then this code...

For Each R In Range("F4:M4")
Debug.Print R.Value, Cells(1, R.Column).Value
Next

will iterate *across* Row 4 from cell F4 to M4 and prints to the Immediate
window the contents of what is in each cell of F4:M4 along with the contents
of Row 1 for that cell's same column. Does that help you any?

--
Rick (MVP - Excel)


"MJKelly" wrote in message
...

Rick,
True the header row will always be row 1.

I'm still not sure how to write the code to reflect this however?

Matt

Rick,

Thanks for the help.

The following line works when I only query one row. As soon as I
attempt to loop through multiple rows nothing happens.
r.Value = Cells(3, c.Column).Value

I have a table headed with ten minute time windows. I want each row
to be checked so the time of the first task (first nonblank
timewindow) is input to column B of the same row. Ithink the error
lies in the third row of code below? How do I loop through a range
which is based on an offset of the current cell?

ThisWorkbook.Sheets("Monday").Select
For Each r In Range("D4:D10")
For Each c In Range(r.Address).Offset(0,
4).Range(r.Address).Offset(0, 147)
If Not c.Value = "" Then
r.Value = Cells(3, c.Column).Value
Exit For
Else
End If
Next c
Next r

Hope this makes it clerarer?
Matt

I am not entirely sure what range you intended this line to cover...

For Each c In Range(r.Address).Offset(0, 4).Range(r.Address).Offset(0, 147)

Seems to be too many dotted values to me. Are you looking to cover columns H
through EU by any chance? If so, try this statement in place of the one
above and see if that does what you want...

For Each c In Range(R.Offset(0, 4), R.Offset(0, 147)).Address

--
Rick (MVP - Excel)


"MJKelly" wrote in message
...

Rick,

Thanks for the help.

The following line works when I only query one row. As soon as I
attempt to loop through multiple rows nothing happens.
r.Value = Cells(3, c.Column).Value

I have a table headed with ten minute time windows. I want each row
to be checked so the time of the first task (first nonblank
timewindow) is input to column B of the same row. Ithink the error
lies in the third row of code below? How do I loop through a range
which is based on an offset of the current cell?

ThisWorkbook.Sheets("Monday").Select
For Each r In Range("D4:D10")
For Each c In Range(r.Address).Offset(0,
4).Range(r.Address).Offset(0, 147)
If Not c.Value = "" Then
r.Value = Cells(3, c.Column).Value
Exit For
Else
End If
Next c
Next r

Hope this makes it clerarer?
Matt