Changing date to next day based on previous cell

**Willmannyeatthat** · 03-20-2012, 09:24 AM

Greetings All,

I very much plan on being a part of this community once I start getting good at this stuff. Right now I have a worksheet (action sheet) that can pick out dates in columns A and E. All these dates are in sequential order from left to right and top to bottom. What I am looking to do (though VBA) is if a row is inserted, and new sequential dates are added, all the dates afterwards will update to the next corresponding day. I know this sounds confusing so I will attach the spreadsheet to show you what I mean. Thanks for you help (you awesome awesome people in the internets).
Project Team Control Document4.xlsm

(Edit) Going to edit the post as a gain more progress. Here is what I'm aiming for:

Sub DATEADD()
If (A7 < E6) Then
A7 = DATEADD("d", 1, CDate(E6))

End If

End Sub

I know this looks basic compared to the rest of that worksheet but much of that worksheet was copy and pasting.

Edit2:
A pic to show what I mean:
worksheet display.jpg

**Dennis7849** · 03-20-2012, 10:10 AM

I don't think you use Cell references that way. Use the Range object.

Sub DATEADD()

    If (Range("A7").Value < Range("E6").Value) Then
         Range("A7").Value = DATEADD("d", 1, CDate(Range("E6").Value))
    End If

End Sub

In stead of hard coding the cells like this, why not just re-sequence all the dates

Like this;

StartRow = 2
EndRow = 20

MyDate=CDate("1/1/2012")
For MyRow = StartRow to EndRow

   Cells(MyRow,1).Value=MyDate
   MyDate = DATEADD("d", 1, MyDate)
   Cells(MyRow,5).Value=MyDate
   MyDate = DATEADD("d", 1, MyDate)

Next MyRow

**Willmannyeatthat** · 03-20-2012, 10:17 AM

Thank you for the speedy reply. I can't say that I fully understand what re-sequencing would do but I do need it to be dynamic enough to be able to change if the date were to change. The dates will change often so that is why I opted on using the cell values.