Filling dates from start Date to End date

[silentwolf]

Hi guys,

I am not able to get this simple code running..

Sub FillDates()
    Dim datStart As Date
    Dim datEnd As Date
    Dim row As Long
    
    datStart = Range("E1").value
    datEnd = Range("E2").value
    
    For row = 5 To datEnd - datStart
'        Cells(row, 2) = datStart + row - 1
'        Cells(row, 2) = datStart - 1
'        Cells(row, 2) = datStart + 1
        Cells(row, 2) = datStart + 1
        
    Next row
End Sub

all those things I tried but no luck to get the dates filled from "B5") for the first date down to the last date.

As I am sure it is simple but I can not get it to run correctly.

Maybe someone can help.

Thanks

 

[GirishDhruva]

@silentwolf try with the below code

untested

Sub FillDates()
    Dim datStart As Date
    Dim datEnd As Date
    Dim row As Long
    
    datStart = Range("E1").Value
    datEnd = Range("E2").Value
    
    For row = 5 To datEnd - datStart
'        Cells(row, 2) = datStart + row - 1
'        Cells(row, 2) = datStart - 1
'        Cells(row, 2) = datStart + 1
        Cells(row, 2) = datStart + 1
        datStart = datStart + 1


        
    Next row
End Sub

Regards,
Dhruva

 

[silentwolf]

Hi,
thanks for your reply.

this is what i got now..

Sub FillDates()
    Dim datStart As Date
    Dim datEnd As Date
    Dim row As Long
    
    datStart = Range("E1").value
    datEnd = Range("E2").value
    
    For row = 5 To datEnd - datStart
        Cells(row, 2) = datStart
        datStart = datStart + 1
    Next row
End Sub

however it starts at the right date but ends with 5 days less then the date what I am looking for.. (

datStart = 6.Aug.2019
datEnd =31.Aug.2019

but in the code it starts at 6.Aug.2019 but stops at the 26 of August..

 

[Rick Rothstein]

You could simply iterate the dates and add a row counter variable to place those dates...

Sub FillDates()
  Dim D As Long, R As Long
  R = 4
  For D = Range("E1").Value To Range("E2").Value
    R = R + 1
    Cells(R, 2).NumberFormat = "d.mmm.yyyy"
    Cells(R, 2).Value = D
  Next
End Sub

 

[footoo]

Sub FillDates()
    Dim datStart As Date
    Dim datEnd As Date
    Dim row As Long
    
    datStart = Range("E1").Value
    datEnd = Range("E2").Value
    Cells(5, 2) = datStart
    For row = 6 To datEnd - datStart + 5
        Cells(row, 2) = Cells(row - 1, 2) + 1
    Next row
End Sub

 

[GirishDhruva]

then add 4 in loop

Sub FillDates()
    Dim datStart As Date
    Dim datEnd As Date
    Dim row As Long
    
    datStart = Range("E1").value
    datEnd = Range("E2").value
    
    For row = 5 To datEnd - datStart + 4
        Cells(row, 2) = datStart
        datStart = datStart + 1
    Next row
End Sub

 

[silentwolf]

Thanks Rick and footoo and ofcourse GirishDhruva too!!

Learned again more about it!

Cheers

 

[Peter_SSs]

You could also do it without any looping or variables

Sub FillDates_v2()
  Range("B5").Value = Range("E1").Value
  Range("B5").DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=xlDay, Step:=1, Stop:=Range("E2").Value
End Sub

 

[Rick Rothstein]

You could also do it without any looping or variables

Sub FillDates_v2()
  Range("B5").Value = Range("E1").Value
  Range("B5").DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=xlDay, Step:=1, Stop:=Range("E2").Value
End Sub

I would use your code, but just to show there is almost always multiple ways to do things in VBA, here is another non-looping macro...

Sub FillDates_v2()
  With [B5].Resize([E2] - [E1] + 1)
    .NumberFormat = "[B][COLOR="#FF0000"]m/d/yyyy[/COLOR][/B]"
    .Value = Evaluate("ROW(" & [N(E1)] & ":" & [N(E2)] & ")")
  End With
End Sub

Note: Change the red highlighted text to set the date display format of your choosing.

 

[silentwolf]

Thanks guys,
for your input and different approaches for the code ..
I would of been happy to be able to worked one out of myself gg .-)