Offsetting to identify value in header row

[MJKelly]

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("D41000")
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 Rothstein]

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?

 

[MJKelly]

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 Rothstein]

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?

 

[MJKelly]

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("D410")
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

 

[Rick Rothstein]

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

 

[MJKelly]

Excellent Thanks again Rick. I did get an error stating "For each may
only iterate over a collection object or an array", but removing
the .address from the end sorted that.

I do now have to loop through the same data to find the end time. I
thought I could work this out for myself, by using the same code but
working backwards through the offset range, but, i'm stuck - how do I
work through a range in reverse?

Regards,
Matt

 

[Rick Rothstein]

Sorry about the Address problem... I used that in testing my code and then
stuck it into the For Each statement without realizing I had left the
Address property call in there. I'm glad you were able to discover my error
and work around it.

Okay, now about your code... I hadn't actually looked at what your code was
doing... I simply pointed out something that looked like a syntax problem
and suggested a way around that problem. If I understand what you are trying
to do correctly (place in Columns D and E the start and end date from Row 1
for the first and last piece of data in Columns H through EU), then I think
this code will do that...

Sub GetStartStopDates()
Dim R As Range
For Each R In Worksheets("Sheet3").Range("D41000")
R.Value = Cells(1, Cells(R.Row, "G").End(xlToRight).Column).Value
R.Offset(, 1).Value = Cells(1, Cells(R.Row, Worksheets("Sheet3"). _
Columns.Count).End(xlToLeft).Column).Value
Next
End Sub

 

[MJKelly]

Amazing thanks Rick.
I'm new to VBA (and programming in general) and must say I find this
group a real help.

I learning quickly though. I had not even considered using the column
number and .end commands.

I used the following code in the end as I had an issue where the
startdate was a in a cell ajoined to another part of the table so the
xltoright missed the startdate and went to the end date: -


identify startdate
For Each R In Range("D41000")
For Each c In Range(R.Offset(0, 4), R.Offset(0, 147))
If Not c.Value = "" Then
R.Value = Cells(3, c.Column).Value
Exit For
Else
End If
Next c
Next R

identify Enddate
For Each R In Worksheets("Monday").Range("E4:E1000")
R.Value = Cells(3, Cells(R.Row, Worksheets("Monday"). _
Columns.Count).End(xlToLeft).Column + 1).Value
Next

Many thanks again,
Matt