vba - 生成给定开始日期和结束日期的日期列表

Question

之前，我发现 Andy Brown 完成的一些 VBA 代码生成了一个列表，并使每个日期成为另一个用户的第一个或第 15 个。我试图根据我的需要调整此代码，但我正在努力。目前，代码一旦运行，就会一遍又一遍地输入相同的日期，我必须结束 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

上面列出了我基于我的模型的以前的代码，下面列出了我最可能糟糕的代码:

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

我需要的是生成给定开始日期和结束日期之间的每个 日期，而不仅仅是第 15 天和第 1 天。这是怎么做到的？

Answer 1

编辑:

正如评论中所讨论的，这显然是您所需要的。

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

或者，For 循环也可以。

截图:

进一步编辑:

水平版，根据要求。

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

截图:

注意:我还修复了垂直版本以在提供的结束日期停止。