Date loop overflow

Question

I have two dates, both in a named cell. Range("NewStartDate").Value = 24/07/2022 and Range("FinishDate").Value = 31/12/2023

I need all columns to have heading which is a date 7 days after previous column, i.e. A1 is NewStartDate, B1 is NewStartDate+7, C1 is NewStartDate + 7*2, etc. and it will end once we reach the FinishDate.

I created this loop

Sub FillInDates()


Dim i as Integer, d as Date, x as Date


Range("NewStartDate").Value = "24/07/2022"
Range("FinishDate").Value = "31/12/2023"

d = Range("NewStartDate").Value
i = 1

Do While x < FinishDate
     Range("NewStartDate").Offset(0, i).Value = DateSerial(Year(d), Month(d), Day(d) + (7*i)
     x = Range("NewStartDate").Offset(0, i).Value
     i = i + 1
Loop 

End Sub

It fills in the following column with the correct next week, however it never stops and I get an overflow error. Why is it not able to stop once we get past end date??

Answer 1

I can't reproduce your error but I can recommend using arrays instead of interacting with the spreadsheet one cell at a time - it is much faster.

Your code could look like this:

Sub FillInDates()

  Dim StartDate As Date
  Dim FinishDate As Date
  
  StartDate = Range("NewStartDate")
  FinishDate = Range("FinishDate")
  
  Dim i As Long
  Dim DateArray As Variant
  
  ReDim DateArray(1 To 1, 1 To Int((FinishDate - StartDate) / 7)) As Variant
  
  For i = 1 To UBound(DateArray, 2)
    DateArray(1, i) = StartDate + i * 7
  Next i
  
  Range("NewStartDate").Offset(0, 1).Resize(1, UBound(DateArray, 2)) = DateArray

End Sub