Loop to iterate through dates until cell value

[WaterRulesEverything]

Attempting to loop though dates using VBA but I'm getting an infinite loop. Cell A1 is the start date, cell A2 is the date incremented by 14 days, cell B2 is the stop date. I just want the dates to loop and increment by 14 days and stop when the date reaches cell B2. The incremented dates will continue to update in cells A1 and A2.

1/26/2021​
2/8/2021​	8/20/21​

This code below increments the date fine, but I am getting an infinite loop where the code will continue to run past the set date in cell B2

Sub Button1_Click()
Application.ScreenUpdating = True
StartDate = Range("A2").Value
EndDate = Range("B2").Value
'Do Until Range("A2").Value = Range("B2").Value
Do While StartDate < EndDate
Range("A1").Value = DateAdd("d", 1, CDate(Range("A2")))
Range("A2").Value = DateAdd("d", 14, CDate(Range("A2")))
Loop
  
End Sub

 

[Joe4]

That is because you are setting your StateDate and EndDate values above the loop.
So they are assigned initial values, and NEVER changed (because you are assigning values, not cell references to them).

Try using named ranges instead, i.e.

Sub Button1_Click()

Application.ScreenUpdating = True

Dim StartDate As Range
Dim EndDate As Range

Set StartDate = Range("A2")
Set EndDate = Range("B2")

Do While StartDate.Value < EndDate.Value
    Range("A1").Value = DateAdd("d", 1, StartDate)
    Range("A2").Value = DateAdd("d", 14, StartDate)
Loop
 
End Sub

 

[WaterRulesEverything]

That is because you are setting your StateDate and EndDate values above the loop.
So they are assigned initial values, and NEVER changed (because you are assigning values, not cell references to them).

Try using named ranges instead, i.e.

Sub Button1_Click()

Application.ScreenUpdating = True

Dim StartDate As Range
Dim EndDate As Range

Set StartDate = Range("A2")
Set EndDate = Range("B2")

Do While StartDate.Value < EndDate.Value
    Range("A1").Value = DateAdd("d", 1, StartDate)
    Range("A2").Value = DateAdd("d", 14, StartDate)
Loop
 
End Sub

This worked, thank you so much! I'm still new to VBA and it was good to know my mistake. Thanks again!

 

[Joe4]

You are welcome.

I hope you understand why your original way didn't work.
You were setting those values to hard-coded numbers (not assigning them to cells).
If you had wanted to do it that way, you would also have needed to updated those variables inside of your loop (which isn't necessary if you using named ranges instead of numeric variables).

Let me know if you have any questions about it.