Consulting

Results 1 to 14 of 14

Thread: Date Series Fill

  1. #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. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,272
    Location
    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 expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Quote Originally Posted by psctornado View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Quote Originally Posted by xld View Post
    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. #5
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #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. #7
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,272
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #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!

    Quote Originally Posted by mdmackillop View Post
    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. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    After the loop, change the value of dt.Offset(i). Either clear it or set it to the desired value.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    Quote Originally Posted by mdmackillop View Post
    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. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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)
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  13. #13
    Quote Originally Posted by mdmackillop View Post
    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. #14
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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)
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

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