how to get the following output(reference image below) using VBA for getting list of dates from a particular date to particular date?

[abhi221996]

HOW CAN I GET THIS OUTPUT USING VBA?

 

[bebo021999]

Assums with giving start and end date:

Sub test()
Dim startD&, endD&, k&
startD = #12/15/2021#
endD = #12/31/2022#
For Each cell In Range("A4").Resize(endD - startD, 1)
k = k + 1
cell.Value = startD + k
Next
End Sub

 

[Saurabhj]

Hi Abhi,

Use below code:

Sub showDates()

Dim fromDate As Date, toDate As Date, myDate As Date
Dim rowno As Integer

rowno = 1

fromDate = #12/15/2021#
toDate = #1/10/2022#

myDate = fromDate
Do While myDate <= toDate
    Worksheets("Sheet1").Cells(rowno, 1) = myDate
    myDate = myDate + 1
    rowno = rowno + 1
Loop

End Sub

 

[abhi221996]

Thanks all the solutions.
is it possible to do it just by for loop.

 

[Saurabhj]

Using For Loop:

Sub showDatesFor()

Dim fromDate As Date, toDate As Date, myDate As Date
Dim rowno As Integer

rowno = 1

fromDate = #12/15/2021#
toDate = #1/10/2022#

For myDate = fromDate To toDate
    Worksheets("Sheet1").Cells(rowno, 1) = myDate
    rowno = rowno + 1
Next

End Sub

 

[abhi221996]

Thank you Saurabh.
I got a bit understanding how loops works.

 

[bebo021999]

Thanks all the solutions.
is it possible to do it just by for loop.

Hi, "For Each" is one of "loop" but using specific object: "range"

For Each cell In Range("A4").Resize(endD - startD, 1)
k = k + 1
cell.Value = startD + k
Next

 

[abhi221996]

Understood! thanks bebo021999
I just wanted to know how to do it by "for" loop.

 

[abhi221996]

Hello all!
I have learned a new function called Dateadd how can i do it by that please suggest?

 

[Saurabhj]

Hello Abhi,

Below code we can use if would like to use DateAdd method.

Sub showDates()

Dim fromDate As String, toDate As String
Dim rowno As Integer

fromDate = "12/15/2021"
toDate = "10/1/2022"

Worksheets("Sheet2").Cells(1, 2) = fromDate

For rowno = 1 To DateDiff("d", fromDate, toDate)
    Worksheets("Sheet2").Cells(rowno + 1, 2) = DateAdd("d", 1, Sheets("Sheet2").Cells(rowno, 2))
Next

End Sub