1. ## Date Series Fill

Hi All,

I was looking for some regarding loops. I consider myself decently proficient with VBA, but I have never written a loop. My goal is pretty simple. Say I have a date of 08/15/2017, I would like to run a loop where my end date (max date) is located in cell D3, & I want to add 1 year to the cell above. So if I have 08/15/2017 in cell A2, in B2, and so on down I'd like to have 1 year added to the date in the row above (09/15/2017, 10/15/2017 etc).

I've looked around with how to write loops, but it seems like I'm missing something & i'm not sure what that something is. Any help would be greatly appreciated!!

Thanks!

2. What is missing is an example of your code.

```For  i = 1 to 3
msgbox i
Next```
```Do
i = i + 1
msgbox i
Loop While i <= 3```
```Do While i <= 3
i = i + 1
msgbox i
Loop```
```Do While True
msgbox "Hi there"

i = i + 1
If i >=3 then exit do
Loop```

I consider myself decently proficient with VBA, but I have never written a loop.
Don't wish to be rude, but in my eyes that is a total contradiction.

4. Originally Posted by xld
Don't wish to be rude, but in my eyes that is a total contradiction.
No offense taken. I should know how to do loops, it's just not something I've ever had to come across. I completely agree that even proficient is a reach! Below is the code I started with, but it's not giving me anything. Again any help would be appreciated.

A2 = 08/15/2017
A3 and down I want to be 1 year added to the previous cell until the value in D3 is met.

```Private Sub dates_Click()Dim i As Integer
For i = A2 To D3
Cells(i, A2).Value = "DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))"
Next i
End Sub```

5. ```Sub Test()
Set dt = Range("A2")
Do
i = i + 1
dt.Offset(i) = DateSerial(Year(dt) + i, Month(dt), Day(dt))
Loop Until dt.Offset(i) >= Range("D3")
End Sub```

6. Thanks MDMack!

The code worked perfectly. Could you advise as how to adapt that code to work on additional columns & rows? i attempted to modify the code to work on another row, but it's giving me a debug error.

```Private Sub test_Click()
Set dt = Range("A2")
Set dt2 = Range("B2")
Do
i = i + 1
For j = 1 To 2
dt.offset(i) = DateSerial(Year(dt) + i, Month(dt), Day(dt))
dt2.offset(j) = DateSerial(Year(dt) + i, Month(dt2), Day(dt2))
Loop Until dt.offset(i) >= Range("D3")
Loop Until dt2.offset(j) >= Range("D4")
End Sub```

7. ```Sub DateSeries()
'
' DateSeries Macro 10/1/2017 by SamT
'

Range("A2").DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlYear, Step:=1, Stop:=CDate(Range("D2")), Trend:=False
End Sub```

8. This will fill in dates below A2 : D2 using date limits in P3:P6
```Sub Test()
For j = 0 To 3
Set dt = Range("A2").Offset(, j)
i = 0
Do
i = i + 1
dt.Offset(i) = DateSerial(Year(dt) + i, Month(dt), Day(dt))
Loop Until dt.Offset(i) >= Range("P3").Offset(j)
Next j
End Sub```

9. Thanks Mdmack, the code works flawlessly. One last thing I'm curious on. Do you know how I could go about setting the last date in the loop in the 2nd column to equal a specified date. Currently, the code adds 1 year to the prior to cell until the loop meets the value in P3. I was looking to see if the code could be modified so that the end date in column B would be a specific date I have in column P4. Currently if I put a value in P4, say 12/01/2017, the code returns a value of 10/01/2018, since that is 1 yr past the value in the cell above. Any help again would be greatly appreciated!

Originally Posted by mdmackillop
This will fill in dates below A2 : D2 using date limits in P3:P6
```Sub Test()
For j = 0 To 3
Set dt = Range("A2").Offset(, j)
i = 0
Do
i = i + 1
dt.Offset(i) = DateSerial(Year(dt) + i, Month(dt), Day(dt))
Loop Until dt.Offset(i) >= Range("P3").Offset(j)
Next j
End Sub```

10. After the loop, change the value of dt.Offset(i). Either clear it or set it to the desired value.

11. Originally Posted by mdmackillop
After the loop, change the value of dt.Offset(i). Either clear it or set it to the desired value.
Thanks again for the help. If I change that value though it changes the end value in column A, but I'm looking to change the end value in column B, while also adding a year prior to that in the rows above.

Example :

Row 2 : A2) 10/01/2014 ; B2) 09/30/2015
Row 3 : A3) 10/01/2015 ; B3) 09/30/2016
Row 3 : A4) 10/01/2016 ; B4) 05/01/2017

Thanks again for all the help!

12. dt.Offset(i) applies to each column. You can compare this to your terminal value and amend it accordingly. You could also amend previous or following values by adjusting dt.Offset(i-1) or dt.Offset(i+1)

13. Originally Posted by mdmackillop
dt.Offset(i) applies to each column. You can compare this to your terminal value and amend it accordingly. You could also amend previous or following values by adjusting dt.Offset(i-1) or dt.Offset(i+1)
I really appreciate the help. I haven't been able to modify the code to have the 2nd column equal the last date. Any ideas?

14. ```Do            i = i + 1
dt.Offset(i) = DateSerial(Year(dt) + i, Month(dt), Day(dt))
Loop Until dt.Offset(i) >= Range("F3").Offset(j)
dt.Offset(i) = Range("F3").Offset(j)```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•