looping a macro

[johngio]

Hi all,

I have created a list of dates in the A column. I have created a list of times in the B column. I.e.

11/10/2006 6
11/10/2006 12
11/10/2006 14
11/10/2006 18
12/10/2006 5
12/10/2006 11
12/10/2006 14


These values may run from Row 1 through to Row 40.

I have in column C and E (respectively) dates and times in the following format:

11/10/2006_____1
______________2
______________3
______________4
______________5
______________6
______________7
______________8
______________9
______________10
______________11
______________12
______________13
______________14
______________15
______________16
______________17
______________18
______________19
______________20
______________21
______________22
______________23
______________24
12/10/2006_____1
______________2
______________3
______________4
______________5
______________6
______________7
______________8
______________9
______________10
______________11
______________12
______________13
______________14
______________15
______________16
______________17
______________18
______________19
______________20
______________21
______________22
______________23
______________24


What I would like to do is for each time and date in columns A and B, enter them in the F column for the corresponding date and time. The output should look like:



11/10/2006_____1
______________2
______________3
______________4
______________5
______________6_____11/10/2006 6
______________7
______________8
______________9
______________10
______________11
______________12_____11/10/2006 12
______________13
______________14_____11/10/2006 14
______________15
______________16
______________17
______________18_____11/10/2006 18
______________19
______________20
______________21
______________22
______________23
______________24
12/10/2006_____1
______________2
______________3
______________4
______________5_____12/10/2006 5
______________6
______________7
______________8
______________9
______________10
______________11_____12/10/2006 11
______________12
______________13
______________14_____12/10/2006 14
______________15
______________16
______________17
______________18
______________19
______________20
______________21
______________22
______________23
______________24


*Note; the cells in the C column are merged, such that if you find the correct date and offest one to the right you will arrive at 1 in the E column.

Any ideas on how to create such a loop?

Thanks

John

 

[johngio]

Tony,

It's working now. I had the wrong date in the B column.

How embarrassment :P

 

[johngio]

It turns out I do need a dynamic reference to the AG column as this value will be changing.

In the above formula you stated:

For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

I changed this to:

For Each ce In Range("AG14:AG" & Cells(Rows.Count, 1).End(xlUp).Row)

Due to the fact that my first data will always appear in cell AG14. This however seems to only see the first piece of data and ignore everything after cell AG14. The data in column AG14 is:

11/10/2006
11/10/2006
11/10/2006
11/10/2006
12/10/2006
12/10/2006
12/10/2006

Any ideas?

Thanks

John

 

[acw]

John


try

For Each ce In Range("AG14:AG" & Cells(Rows.Count, "AG").End(xlUp).Row

The 1 in the original code referred to column 1 (ie column A). In the revised code, you can either use the column number (33) or the alpha "AG".

Tony

 

[johngio]

Cool

Thanks!