Filling dates from start Date to End date

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,216
Office Version
  1. 2016
Hi guys,

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

Code:
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
@silentwolf try with the below code

untested

Code:
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
 
Upvote 0
Hi,
thanks for your reply.

this is what i got now..

Code:
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..

:eeek:
 
Upvote 0
You could simply iterate the dates and add a row counter variable to place those dates...
Code:
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
 
Last edited:
Upvote 0
Code:
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
 
Upvote 0
then add 4 in loop

Code:
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
 
Last edited:
Upvote 0
Thanks Rick and footoo and ofcourse GirishDhruva too!!

Learned again more about it!

Cheers
 
Last edited:
Upvote 0
You could also do it without any looping or variables
Code:
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
 
Upvote 0
You could also do it without any looping or variables
Rich (BB code):
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...
Code:
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.
 
Upvote 0
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 .-)
 
Upvote 0

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top