Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
scheduling dates nested loops ...
Some help please, I have inherited some files which I am
trying to update. I have gotten to this point and now seem to be stuck. I have put a sample and the expected result after the macro is run Names old date new date difference name1 17-May-05 17-Nov-05 184 name1 17-May-05 17-Nov-05 184 name1 17-May-05 17-Nov-05 184 name2 17-May-05 17-Nov-05 184 name2 17-May-05 17-Nov-05 184 name2 17-May-05 17-Nov-05 184 name3 17-May-05 17-Nov-05 184 name2 17-May-05 17-Nov-05 184 name3 17-May-05 17-Nov-05 184 .... Dim cell As Range, cell1 As Range, odate As Date, ndate As Date Set cell = ActiveWorkbook.Worksheets("Sheet1").Cells(3, 1) Set cell1 = ActiveWorkbook.Worksheets("Sheet1").Cells(3, 4) odate = cell.Offset(-1, 1).Value ndate = cell.Offset(-1, 2).Value Do Until IsEmpty(cell1.Value) While cell = cell.Offset(-1, 0) odate = ndate ndate = DateAdd("m", 6, odate) cell1.Offset(0, -2) = odate cell1.Offset(0, -1) = ndate cell1 = cell1.Offset(0, -1) - cell1.Offset(0, -2) Set cell = cell.Offset(1, 0) Set cell1 = cell1.Offset(1, 0) Wend Set cell = cell.Offset(1, 0) Set cell1 = cell1.Offset(1, 0) what I am trying to do with the code is to change the sample table to the following Name old date new date difference name1 17-May-05 17-Nov-05 184 name1 17-Nov-05 17-May-06 181 name1 17-May-06 17-Nov-06 184 name2 17-May-05 17-Nov-05 184 name2 17-Nov-05 17-May-06 181 name2 17-May-06 17-Nov-06 184 name3 17-May-05 17-Nov-05 184 name2 17-Nov-05 17-May-06 181 name3 17-May-06 17-Nov-06 184 .... however, when the name changes the date continues incrementing. How do I start over incrementing dates for every new name Any suggestions / help would be greatly appreciated -- thanks as always for the help jer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
scheduling dates nested loops ...
got it
-- thanks as always for the help "jer" wrote: Some help please, I have inherited some files which I am trying to update. I have gotten to this point and now seem to be stuck. I have put a sample and the expected result after the macro is run Names old date new date difference name1 17-May-05 17-Nov-05 184 name1 17-May-05 17-Nov-05 184 name1 17-May-05 17-Nov-05 184 name2 17-May-05 17-Nov-05 184 name2 17-May-05 17-Nov-05 184 name2 17-May-05 17-Nov-05 184 name3 17-May-05 17-Nov-05 184 name2 17-May-05 17-Nov-05 184 name3 17-May-05 17-Nov-05 184 ... Dim cell As Range, cell1 As Range, odate As Date, ndate As Date Set cell = ActiveWorkbook.Worksheets("Sheet1").Cells(3, 1) Set cell1 = ActiveWorkbook.Worksheets("Sheet1").Cells(3, 4) odate = cell.Offset(-1, 1).Value ndate = cell.Offset(-1, 2).Value Do Until IsEmpty(cell1.Value) While cell = cell.Offset(-1, 0) odate = ndate ndate = DateAdd("m", 6, odate) cell1.Offset(0, -2) = odate cell1.Offset(0, -1) = ndate cell1 = cell1.Offset(0, -1) - cell1.Offset(0, -2) Set cell = cell.Offset(1, 0) Set cell1 = cell1.Offset(1, 0) Wend Set cell = cell.Offset(1, 0) Set cell1 = cell1.Offset(1, 0) what I am trying to do with the code is to change the sample table to the following Name old date new date difference name1 17-May-05 17-Nov-05 184 name1 17-Nov-05 17-May-06 181 name1 17-May-06 17-Nov-06 184 name2 17-May-05 17-Nov-05 184 name2 17-Nov-05 17-May-06 181 name2 17-May-06 17-Nov-06 184 name3 17-May-05 17-Nov-05 184 name2 17-Nov-05 17-May-06 181 name3 17-May-06 17-Nov-06 184 ... however, when the name changes the date continues incrementing. How do I start over incrementing dates for every new name Any suggestions / help would be greatly appreciated -- thanks as always for the help jer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with nested for loops | Excel Discussion (Misc queries) | |||
Help with nested for loops | Excel Worksheet Functions | |||
nested for loops and end for | Excel Programming | |||
Help on nested loops | Excel Programming | |||
Nested loops?? | Excel Programming |