Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offsetting to identify value in header row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offsetting to identify value in header row
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offsetting to identify value in header row
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offsetting to identify value in header row
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offsetting to identify value in header row
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offsetting to identify value in header row
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offsetting to identify value in header row
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offsetting to identify value in header row
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("D4:D1000") 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offsetting | Excel Discussion (Misc queries) | |||
identify sheet number in header? | Excel Discussion (Misc queries) | |||
How Do I Identify 1 to 1 Offsetting Entries in a List? | Excel Programming | |||
Offsetting a varible?? | Excel Programming | |||
Identify Cell by Column Header and ID in Col A (Track Changes) | Excel Programming |