to input date between start and end date

I have a method to input a start and end date using user form. How can I input the dates in between this start and end date and provide it as an input in excel sheet using VBA?

For example, if my start date 1st jan 2012 and my end date is 1st april 2012, I would like to get input in an excel sheet in this form :

1st jan 2012
1st feb 2012
1st march 2012
1st april 2012

Please, suggest me a VBA code to do the same.

1 answer

  • answered 2018-10-17 08:11 Error 1004

    It s difficult to understand what do you mean but you can try this:

    Sub test3()
    
    Dim Startdate As Date, EndDate As Date, AdditionalDate As String
    Dim DatesDiff As Integer
    Dim i As Integer
    Dim LastRowA As Long
    
    Startdate = Format(CDate("01/01/12"), "DD/MM/YYYY")
    EndDate = Format(CDate("01/04/12"), "DD/MM/YYYY")
    
    If Sheet1.Range("A1").Value = "" Then
        Sheet1.Range("A1").Value = Startdate
    Else
        LastRowA = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
        Sheet1.Range("A" & LastRowA + 1).Value = Startdate
    End If
    
    DatesDiff = Month(EndDate) - Month(Startdate) - 1
    
    For i = 1 To DatesDiff
        AdditionalDate = Left(CStr(Startdate), 2) & "/" & (Month(CStr(Startdate)) + i) & "/" & Right(CStr(Startdate), 4)
        LastRowA = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
        Sheet1.Range("A" & LastRowA + 1).Value = CDate(AdditionalDate) 'Format(CDate(AdditionalDate), "DD/MM/YYYY")
    Next i
        LastRowA = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
        Sheet1.Range("A" & LastRowA + 1).Value = EndDate
    
    End Sub