+ Reply to Thread
Results 1 to 9 of 9

VBA Needed to help add an automatic function to add a new day in the next row.

Hybrid View

  1. #1
    Registered User
    Join Date
    03-25-2012
    Location
    windham, me
    MS-Off Ver
    Excel 2003
    Posts
    8

    Question VBA Needed to help add an automatic function to add a new day in the next row.

    Hello, I am looking for help to add a function or formula to generate a list of days from a start date to current date and have a new day in the next row in the spreadsheet.

    I am trying to put together a simple daily interest sheet where the spread sheet will generate a list of days from a given start date to what the current date might be. The daily list can go for more than 1 year. I am including a copy of the spreadheet i have so far for some to look at and see if they have a way to solve my issue with date generation for each new line item.

    Thanks for Helping.Daily simple interest.xls

  2. #2
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: VBA Needed to help add an automatic function to add a new day in the next row.

    WYETTE,

    You could use the following formula to calculate the next day.

    =DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)

    where A1 would contain the start date.

  3. #3
    Registered User
    Join Date
    03-25-2012
    Location
    windham, me
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: VBA Needed to help add an automatic function to add a new day in the next row.

    Thank you for the respone. Tried it and it does not do exactly what i need. I have 2 cells that contain a start date and then current date. I am needing the spread sheet to auto generate a new line in the spreadsheet for each day between the start and current date. This sounds simple but it is turning out to be very hard to do. I appreciate the help.

  4. #4
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: VBA Needed to help add an automatic function to add a new day in the next row.

    WYETTE,

    Attached is your original spreadsheet with a few additions.

    Addition #1:

    Cell E5 of the Simple Worksheet now contains a value. I have included a comment in the spreadsheet with an explanation of what the value means.

    Addition #2:

    Cell A11 (First Date in table) is now set to equal cell C4 (Start Date).

    Addition #3:
    Cell A12 now contains the formula I posted previously that determines what the next date is.

    Addition #4:

    Command Button located in Cells A7:B8

    This command button first checks to make sure that cell C4 of the Simple Worksheet has value. If it does not then the VBA code stops. If there is a value then then the command button selects cell A12(contains the date after the starting date) and copies it. It then sets the cells A13 through A(whatever number is contained in cell E5) and pastes the formula into these cells.

    Please let me know if you have any questions or comments.

    Thanks!

    RVASQUEZ

  5. #5
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: VBA Needed to help add an automatic function to add a new day in the next row.

    Sorry I forgot to adjust the code to copy the whole range from A:H. Attached is the spreadsheet that will add the new rows.


    Thanks!

    RVASQUEZ

  6. #6
    Registered User
    Join Date
    03-25-2012
    Location
    windham, me
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: VBA Needed to help add an automatic function to add a new day in the next row.

    Thank you for the help. I tried the new file you corrected. I removed all the previous dates excpet the 2 starting dates and hit the command button and nothing happened. I am using excel 2003 and when it downloaded it said it was chaning the file to a compatabile format. I don't know if that made it not work.

    I do appreciate the help.

  7. #7
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: VBA Needed to help add an automatic function to add a new day in the next row.

    Is Design Mode under the Developer tab clicked? If so, unclick it. Also, if this isn't the case have you enabled macros and content? If not, please do so, this will enable the command button to work.

    I have attached a 2003 compatible version maybe this will help.

    Thanks,

    RVASQUEZ

  8. #8
    Registered User
    Join Date
    03-25-2012
    Location
    windham, me
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: VBA Needed to help add an automatic function to add a new day in the next row.

    Thank you. I turned on Macros, but can not find a content turn on. I also exited out of the Design Mode. But still does not work. But thanks for all you help.

    WYETTE

  9. #9
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: VBA Needed to help add an automatic function to add a new day in the next row.

    Okay let's try and just update your original spreadsheet:

    Open your original document. Not the one I gave you.

    In cell E5 type =C6+11 (since the number of rows that are currently being used before the dates are 11 rows)

    In Cell A11 type =C4 (the start date)

    In Cell A12 type =DATE(YEAR(A11),MONTH(A11),DAY(A11)+1)

    Select Cells A13:A38 and press the delete key to clear the contents. Make sure to not delete the formulas in Cells A11 and A12.

    Then go to the Developer Tab and select the Insert option from the Controls. Select the Command Button under the ActiveX Controls.

    Draw a button above the table. I put it above the Date and Beginning Daily Balance cells.

    Right click on the button and view code.

    Copy and paste the following code in between the Private Sub and the End Sub

        If Range("C4") = "" Then
            Exit Sub
        Else
          Range("a12:H12").Select
            Range("a12:h12").Copy
                Range("a13:h" & Range("e5").Value).PasteSpecial
        End If
    In the end it should look like this

    Private Sub CommandButton1_Click()
        If Range("C4") = "" Then
            Exit Sub
        Else
          Range("a12:H12").Select
            Range("a12:h12").Copy
                Range("a13:h" & Range("e5").Value).PasteSpecial
        End If
    End Sub
    Exit out of VBA

    Unclick the Design Mode Button under the developer tab. Then click the command button to see if it works.

    If it works you'll need to save it as a macro-enable document.

    Let me know if it works for you!

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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