两个日期之间的月末日期数组
•浏览 1
Array of month end dates between 2 dates
在我的工作表中，我有 2 个单元格用于表示最小日期和最大日期。同样在工作表中，我有一个表结构。我想要实现的是在表格末尾附加额外的列，显示这两个日期之间的每个月，包括在内。
例如，最短日期为 2014 年 7 月 31 日，最长日期为 2015 年 6 月 30 日。我想要一个宏来填充列标题 7/31/2014, 8/31/2014, ..., 6/30/2015 到我的表格末尾。
很遗憾，Excel 表格不能有动态标题。然后我想通过有一个日期数组来使用 VBA，然后将 Range.Value 设置为该数组，但无法完全弄清楚如何对其进行编码。
谢谢。
您实际上并不需要 VBA。一个非常简单的 Excel 公式就可以解决问题。
在下面的示例中，单元格 C5 具有 =$C$2。单元格 C6 有这个公式：
=IF(C5>=$C$2,"",DATE(YEAR(C5),MONTH(C5)+2,DAY(0)))Public Sub test()
Dim startDate As Date
Dim endDate As Date
Dim currentDate As Date
startDate = CDate("7/31/2014")
endDate = CDate("6/30/2015")
currentDate = startDate
Do While currentDate <= endDate
MsgBox currentDate
'currentDate = DateAdd("m", 1, currentDate)
currentDate = DateSerial(Year(currentDate), Month(currentDate) + 2, 0)
Loop
End SubSub MAIN()
Dim d1 As Date, d2 As Date, Tbl As Range
d1 = Range("A1").Value
d2 = Range("A2").Value
Set Tbl = Range("B3:E9")
Call setLabels(d1, d2, Tbl)
End Sub
Sub setLabels(dt1 As Date, dt2 As Date, rng As Range)
Dim rToFill As Range, r As Range
Set rToFill = Intersect(rng(1).EntireRow, rng).Offset(-1, 0)
For Each r In rToFill
dv = dt1 + i
r.Value = dv
i = i + 1
If dt1 + i > dt2 Then Exit Sub
Next r
End Subdv = DateSerial(Year(dt1), Month(dt1) + i, Day(dt1))并且，出于本示例的目的，它被复制到单元格 C23。您只需将其复制到您可以想象的最长表格中所需的尽可能远的位置。
解释：公式将前一个日期加上 2 个月，然后取该月的"第 0 天"，这相当于前一个月的最后一天(根据您的示例，我认为这是您想要的) .如果前一个日期已达到最大值，则从那时起它只会写入一个空字符串 ""。
也许你希望这个在一行中，而不是一列；这个想法是一样的。
你想要 DateAdd()
假设你想获取每个月的最后一个日期，你应该使用 DateSerial()
=IF(C5>=$C$2,"",DATE(YEAR(C5),MONTH(C5)+2,DAY(0)))Public Sub test()
Dim startDate As Date
Dim endDate As Date
Dim currentDate As Date
startDate = CDate("7/31/2014")
endDate = CDate("6/30/2015")
currentDate = startDate
Do While currentDate <= endDate
MsgBox currentDate
'currentDate = DateAdd("m", 1, currentDate)
currentDate = DateSerial(Year(currentDate), Month(currentDate) + 2, 0)
Loop
End SubSub MAIN()
Dim d1 As Date, d2 As Date, Tbl As Range
d1 = Range("A1").Value
d2 = Range("A2").Value
Set Tbl = Range("B3:E9")
Call setLabels(d1, d2, Tbl)
End Sub
Sub setLabels(dt1 As Date, dt2 As Date, rng As Range)
Dim rToFill As Range, r As Range
Set rToFill = Intersect(rng(1).EntireRow, rng).Offset(-1, 0)
For Each r In rToFill
dv = dt1 + i
r.Value = dv
i = i + 1
If dt1 + i > dt2 Then Exit Sub
Next r
End Subdv = DateSerial(Year(dt1), Month(dt1) + i, Day(dt1))这是一个基于以下的小例子：
代码如下：
=IF(C5>=$C$2,"",DATE(YEAR(C5),MONTH(C5)+2,DAY(0)))Public Sub test()
Dim startDate As Date
Dim endDate As Date
Dim currentDate As Date
startDate = CDate("7/31/2014")
endDate = CDate("6/30/2015")
currentDate = startDate
Do While currentDate <= endDate
MsgBox currentDate
'currentDate = DateAdd("m", 1, currentDate)
currentDate = DateSerial(Year(currentDate), Month(currentDate) + 2, 0)
Loop
End SubSub MAIN()
Dim d1 As Date, d2 As Date, Tbl As Range
d1 = Range("A1").Value
d2 = Range("A2").Value
Set Tbl = Range("B3:E9")
Call setLabels(d1, d2, Tbl)
End Sub
Sub setLabels(dt1 As Date, dt2 As Date, rng As Range)
Dim rToFill As Range, r As Range
Set rToFill = Intersect(rng(1).EntireRow, rng).Offset(-1, 0)
For Each r In rToFill
dv = dt1 + i
r.Value = dv
i = i + 1
If dt1 + i > dt2 Then Exit Sub
Next r
End Subdv = DateSerial(Year(dt1), Month(dt1) + i, Day(dt1))这是基于 1 天的增量。如果您想要 1 个月的增量，则将其用于 dv
=IF(C5>=$C$2,"",DATE(YEAR(C5),MONTH(C5)+2,DAY(0)))Public Sub test()
Dim startDate As Date
Dim endDate As Date
Dim currentDate As Date
startDate = CDate("7/31/2014")
endDate = CDate("6/30/2015")
currentDate = startDate
Do While currentDate <= endDate
MsgBox currentDate
'currentDate = DateAdd("m", 1, currentDate)
currentDate = DateSerial(Year(currentDate), Month(currentDate) + 2, 0)
Loop
End SubSub MAIN()
Dim d1 As Date, d2 As Date, Tbl As Range
d1 = Range("A1").Value
d2 = Range("A2").Value
Set Tbl = Range("B3:E9")
Call setLabels(d1, d2, Tbl)
End Sub
Sub setLabels(dt1 As Date, dt2 As Date, rng As Range)
Dim rToFill As Range, r As Range
Set rToFill = Intersect(rng(1).EntireRow, rng).Offset(-1, 0)
For Each r In rToFill
dv = dt1 + i
r.Value = dv
i = i + 1
If dt1 + i > dt2 Then Exit Sub
Next r
End Subdv = DateSerial(Year(dt1), Month(dt1) + i, Day(dt1))VBA 具有处理日期的函数。如果您查看链接：
http://software-solutions-online.com/2014/02/21/excel-vba-working-with-dates/
它将向您展示如何使用 VBA 制作数据类型 Date 的变量，这将使您可以轻松地添加和减去月份。
之后，添加方法来创建你想要的列和 .Name 属性来命名列。