Generating a list of dates given the start and end dates

Question

Previously I found some VBA code done by Andy Brown that generates a list and makes each date the first or 15th for another user. I have tried to adjust this code to my needs but I'm struggling. Currently the code, once run, is just putting in the same date over and over and I have to end Excel.

Sub GenerateDates()

Dim FirstDate As Date
Dim LastDate As Date
Dim NextDate As Date

FirstDate = Range("A1").Value
LastDate = Range("a2").Value

NextDate = FirstDate
Range("B1").Select

Do Until NextDate >= LastDate

    ActiveCell.Value = NextDate
    ActiveCell.Offset(1, 0).Select

    If Day(NextDate) = 1 Then
        NextDate = DateAdd("d", NextDate, 14)
    Else
        NextDate = DateAdd("d", NextDate, 20)
        NextDate = DateSerial(Year(NextDate), Month(NextDate), 1)
    End If

Loop

Previous code I based my model upon is listed above and my, most likely terrible code, is below:

Sub GenerateDates()

Dim FirstDate As Date
Dim LastDate As Date
Dim NextDate As Date

FirstDate = Range("startdate").Value
LastDate = Range("enddate").Value

NextDate = FirstDate
Range("tripdays").Select
'selection of columns within one row
Do Until NextDate >= LastDate

    ActiveCell.Value = NextDate
    ActiveCell.Offset(1, 0).Select

    If Day(NextDate) = 1 Then
        NextDate = DateAdd("d", NextDate, 14)

    End If

Loop

End Sub

What I need instead is to generate every date between the given start and end dates, instead of just the 15th and 1st. How is this done?

Solution 1

EDIT:

This is apparently what you need, as discussed in comments.

Sub GenerateDates()

Dim FirstDate As Date
Dim LastDate As Date
Dim NextDate As Date

FirstDate = Range("startdate").Value
LastDate = Range("enddate").Value

NextDate = FirstDate
Range("tripdays").Select
'selection of columns within one row
Do Until NextDate > LastDate

    ActiveCell.Value = NextDate
    ActiveCell.Offset(1, 0).Select
    NextDate = NextDate + 1

Loop

End Sub

Alternatively, a For loop would do just as well.

Screenshot:

FURTHER EDIT:

Horizontal version, as requested.

Sub GenerateDatesH()

Dim FirstDate As Date
Dim LastDate As Date
Dim NextDate As Date
Dim DateOffset As Range
Dim DateIter As Date

FirstDate = Range("startdate").Value
LastDate = Range("enddate").Value
Set DateOffset = Range("tripdays")

For DateIter = FirstDate To LastDate
    DateOffset.Value = DateIter
    Set DateOffset = DateOffset.Offset(0, 1)
Next DateIter

End Sub

Screenshot:

Note: I've also fixed the vertical version to stop at the end date provided.

Solution 2

Avoid using select in code it is very inefficient :)

Sub p()
Dim FirstDate As Date
Dim LastDate As Date
Dim NextDate As Date
Dim r As Long
FirstDate = Range("A1").Value
LastDate = Range("a2").Value
r = 1
Do
 FirstDate = FirstDate + 1
 Cells(r, 2) = FirstDate
 r = r + 1
Loop Until FirstDate = LastDate
End Sub 

to do it in a row replace cells(r,2) by cells(1, r) and start r=2

Comments

[Chardo]

Thank you so much! I have two more questions that I have a feeling will be VERY easy for you. Is there a way to make the output go horizontally instead of down a column? Also, is there a way to make the last date be the end date?

[WitchGod]

Doable. Wait a moment, I'll be adding a variation. :)

[EducateMe]

Far too slow. Never use .Select.

[WitchGod]

@EducateMe This is very old code. But I agree. I don't work with Excel now though but if VB is still the same, then .Select is still bad.