Geek asks and answers

Create a list of dates with start and end dates

Earlier, I found VBA code made by Andy Brown that generates a list and makes each date first or 15th for another user. I tried to tweak this code to my needs, but I'm afraid. Currently, the code once run just enters the same date again and again, and I need to finish 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

The previous code on which I based my model 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

Instead, I need to create each date between the start and end dates, not just 15 and 1. How is this done?

+3
source share
2 answers

EDIT:

This is apparently what you need as described in the 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

, For .

:

enter image description here

:

, .

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

:

enter image description here

. , .

+4

select , :)

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

, (r, 2) (1, r) r = 2

+1

More articles:


All Articles