when adding new line, automatically insert date

[desmond_]

Dear Experts,

is it possible to do following;
I have two cells in which I insert a 'start' date and an 'end' date
now I would like to have excel creating automatically all the rows from the dates in between
its for trip planning purpose

example
[TABLE="width: 500"]
<tbody>[TR]
[TD]enter start date[/TD]
[TD]enter end date[/TD]
[/TR]
[TR]
[TD]From[/TD]
[TD]What[/TD]
[/TR]
[TR]
[TD]=start date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]automatically enter the next date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]automatically enter the next date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=due date[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

thanks a lot in advance
Desmond

 

[Joe4]

Here is a macro that should do that. Just adjust the startDate, endDate, and startCell variables to suit your situation.

Sub MyInsertDateMacro()

    Dim startDate As Date
    Dim endDate As Date
    Dim startCell As Range
    Dim curDate As Date
    Dim counter As Long
    
'   Get start and end dates from cells A1 and B1
    startDate = Range("A1")
    endDate = Range("B1")
    
'   Set which cell to start the dates in
    Set startCell = Range("A2")
    
'   Make sure endDate is not before startDate
    If endDate < startDate Then
        MsgBox "Your end date is prior to the start date!", vbOKOnly, "ERROR!"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
'   Enter dates starting in startCell and moving down column
    curDate = startDate
    counter = 0
    Do Until curDate > endDate
        startCell.Offset(counter, 0) = curDate
        counter = counter + 1
        curDate = curDate + 1
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

 

[desmond_]

works perfectly fine - thanks a lot
may I add another question
I would like to have a summary row after the last date which includes some formulas to calculate the SUM of all above entered dates
this last row should always be placed below the endDate, is that possible ?

thanks in advance
Best Regards

 

[Joe4]

It is just a list of dates. What exactly is being summed?

 

[Joe4]

If your intention is to enter values in column B, so you want to create a sum formula at the bottom of column B, you could add a line of code to do that:

Sub MyInsertDateMacro()

    Dim startDate As Date
    Dim endDate As Date
    Dim startCell As Range
    Dim curDate As Date
    Dim counter As Long
    
'   Get start and end dates from cells A1 and B1
    startDate = Range("A1")
    endDate = Range("B1")
    
'   Set which cell to start the dates in
    Set startCell = Range("A2")
    
'   Make sure endDate is not before startDate
    If endDate < startDate Then
        MsgBox "Your end date is prior to the start date!", vbOKOnly, "ERROR!"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
'   Enter dates starting in startCell and moving down column
    curDate = startDate
    counter = 0
    Do Until curDate > endDate
        startCell.Offset(counter, 0) = curDate
        counter = counter + 1
        curDate = curDate + 1
    Loop
    
[COLOR=#ff0000]'   Add sum to column B after last row
    Cells(counter + startCell.Row, "B").Formula = "=SUM(B" & startCell.Row & ":B" & counter + startCell.Row - 1 & ")"[/COLOR]
    
    Application.ScreenUpdating = True
    
End Sub

 

[desmond_]

thanks a lot for your help!
(nearly forgot to reply and thank you..)

BR desmond