Offsetting to identify value in header row
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("D4:D1000")
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
--
Rick (MVP - Excel)
"MJKelly" wrote in message
...
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
|