Date Series Fill

[psctornado]

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!:banghead:

[SamT]

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

[Bob Phillips]

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.

[psctornado]

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

[mdmackillop]

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

[psctornado]

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

[SamT]

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

[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

[psctornado]

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!


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

[mdmackillop]

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

[psctornado]

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!

[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)

[psctornado]

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?

[mdmackillop]

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)