+ Reply to Thread
Results 1 to 5 of 5

Make row that automatically adds newest month + 2 nearest future months

Hybrid View

  1. #1
    Registered User
    Join Date
    08-01-2014
    Location
    Copenhagen, Denmark
    MS-Off Ver
    2013
    Posts
    7

    Make row that automatically adds newest month + 2 nearest future months

    I have a row with months in the format Jan-14 (01-01-2014), Feb-14 (01-01-2014) and so on, starting from column 3. I want the row to automatically update such that it contains the months from Jan-14 to the present month + two months. That is, since today's date is 25-08-2014, the row should contain the months Jan-14,...., Aug-14 (01-08-2014), Sept-14 (01-09-2014) and Oct-14 (01-10-2014). I have tried to do the following code but it is currently not working. Any suggestions how to make this work?

    Sub Updatemonths()
    Dim col As Long
    Dim lc As Variant
    lc = Worksheets("Summary").Cells(4, Worksheets("Summary").Columns.Count).End(xlToLeft).Column
        Dim StartD As Date, EndD As Date
        StartD = Worksheets("Summary").Cells(4, lc)
        EndD = Date
        For col = 1 To (EndD - StartD)
        If IsDate(Cells(4, lc + col)) Then
            Worksheets("Summary").Cells(4, lc + col).Value = DateSerial(Year(Cells(4, StartD + col)), Month(Cells(4, lc + col)), 1)
        End If
        Next col
    End Sub
    Best regards

    Christian Ulrich
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Make row that automatically adds newest month + 2 nearest future months

    Maybe one of these can help:

    Sub cudh()
    Dim x As Long
    x = ActiveSheet.UsedRange.Columns.count + 1
    Cells(4, x) = Month(Date) & "/" & Right(Year(Date), 2)
    Cells(4, x + 1) = Month(Date + 30) & "/" & Right(Year(Date), 2)
    Cells(4, x + 2) = Month(Date + 60) & "/" & Right(Year(Date), 2)
    Cells(4, "D").Copy
    Range(Cells(4, x), Cells(4, x + 2)).PasteSpecial xlPasteFormats
    End Sub
    Or

    Sub cudhzz()
    Dim x As Long
    Dim i As Long
    Dim y As Long
    x = ActiveSheet.UsedRange.Columns.count + 1
    i = 1
    For y = 4 To x + 2
    Cells(5, y) = MonthName(i) & "/" & Right(Year(Date), 2)
    i = i + 1
    Next y
    Cells(4, "D").Copy
    Range(Cells(5, 4), Cells(5, x + 2)).PasteSpecial xlPasteFormats
    End Sub
    The first extends the range you already have.

    The second starts from Jan-Oct. on row 5.

  3. #3
    Registered User
    Join Date
    08-01-2014
    Location
    Copenhagen, Denmark
    MS-Off Ver
    2013
    Posts
    7

    Re: Make row that automatically adds newest month + 2 nearest future months

    The first code adds the current month + two nearest future month. However, I need the code to add the months conditional of what is already written in the row. Such that e.g. if Aug-14 and Sept-14 are already in the row, it adds Oct-14.

    In the second code I get an error (Run time error 5. Invalid procedure call or argument) on the code part:
    Cells(5, y) = MonthName(i) & "/" & Right(Year(Date), 2)
    But thanks, I can definitely use parts of the code!
    Last edited by cudh; 08-25-2014 at 08:21 AM.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Make row that automatically adds newest month + 2 nearest future months

    Simple AutoFill
    Sub Updatemonths()
        Dim x As Long
        With Worksheets("Sheet1").Cells(4, 4).Resize(, 2)
            .Cells(2).Value = DateAdd("m", 1, .Cells(1).Value)
            x = DateDiff("m", .Cells(1).Value, DateAdd("m", 2, Date) - Day(Date) + 1)
            .AutoFill .Resize(, x + 1)
        End With
    End Sub

  5. #5
    Registered User
    Join Date
    08-01-2014
    Location
    Copenhagen, Denmark
    MS-Off Ver
    2013
    Posts
    7

    Re: Make row that automatically adds newest month + 2 nearest future months

    Thanks. Works like a charm.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 04-09-2014, 08:13 AM
  2. [SOLVED] Calculate future date to nearest Monday
    By mswauger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-09-2013, 04:13 PM
  3. Replies: 1
    Last Post: 11-22-2011, 08:11 AM
  4. Replies: 4
    Last Post: 05-23-2011, 09:15 AM
  5. Counting dates for a the present month but not future months
    By BrianInCalifornia in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-06-2005, 10:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1