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

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