生成给定开始和结束日期的日期列表 [英] Generating a list of dates given the start and end dates

问题描述

以前，我发现一些由Andy Brown完成的VBA代码生成一个列表，并使每个日期成为第一个或第15个为另一个用户。我已经尝试调整这个代码到我的需要，但我正在努力。目前的代码，一旦运行，只是一次又一次地放在同一天，我必须结束Excel。

  Sub GenerateDates （）
 
 Dim FirstDate As Date 
 Dim LastDate As Date 
 Dim NextDate As Date 
 
 FirstDate = Range（A1）。 $ b LastDate = Range（a2）。值
 
 NextDate = FirstDate 
范围（B1）。选择
 
直到NextDate> = LastDate 
 
 ActiveCell.Value = NextDate 
 ActiveCell.Offset（1，0）。选择
 
如果Day（NextDate）= 1然后
 NextDate = DateAdd （d，NextDate，14）
 Else 
 NextDate = DateAdd（d，NextDate，20）
 NextDate = DateSerial（Year（NextDate），Month（NextDate），1） 
结束如果
 
循环
  

以前的代码我基于我的模型以上列出，我的，最可怕的代码如下：

  Sub GenerateDates（）
 
 Dim FirstDate As Date 
 Dim LastDate As Date 
 Dim Ne xtDate As Date 
 
 FirstDate = Range（startdate）。值
 LastDate = Range（enddate）值
 
 NextDate = FirstDate 
范围（tripdays）。选择
'选择一行中的列
 Do Until NextDate> = LastDate 
 
 ActiveCell.Value = NextDate 
 ActiveCell.Offset （1，0）。选择
 
如果Day（NextDate）= 1然后
 NextDate = DateAdd（d，NextDate，14）
 
 End If 
 
循环
 
 End Sub 
  

我需要什么而是在给定的开始和结束日期之间生成 每个 日期，而不是仅在第15和第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 
范围（tripdays）。选择
'选择一行中的列
 Do Until NextDate> LastDate 
 
 ActiveCell.Value = NextDate 
 ActiveCell.Offset（1，0）。选择
 NextDate = NextDate + 1 
 
循环
 
 End Sub 
  

或者，对于 loop也会这样做。

截图：

进一步编辑： / strong>

根据要求的水平版本。

  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）值
 LastDate =范围（enddate）值
设置DateOffset =范围（tripdays）
 
对于DateIter = FirstDate To LastDate 
 DateOffset.Value = DateIter 
设置DateOffset = DateOffset.Offset（0，1）
下一个DateIter 
 
 End Sub 
  

截图

注意：我也修改了垂直版本，在提供的结束日期停止。

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?

解决方案

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.

这篇关于生成给定开始和结束日期的日期列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！