1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003 Insert Row between dates help needed

Discussion in 'Business Applications' started by Strybes, Jan 11, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Hi All

    Does anybody know if it is possible to insert rows between two dates so that the sequence follows.

    Example:
    I have the following 3 dates on 3 seperate lines.

    1/02/2009
    1/07/2009
    1/07/2010

    Is it possible to do a macro or write visual basic code to add rows so that it looks like this:

    1/02/2009
    1/03/2009
    1/04/2009
    1/05/2009
    1/06/2009
    1/07/2009
    1/08/2009
    1/09/2009
    1/10/2009
    1/11/2009
    1/12/2009
    1/01/2010
    1/02/2010
    1/03/2010
    1/04/2010
    1/05/2010
    1/06/2010
    1/07/2010

    Thanks Strybes
     
  2. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Most things to do with data manipulation is possible in Excel. The code below will do it for you if your dates are in Chronological order. The column (of your dates) can be changed by changing the variable 'CX'. HTH.

    Code:
    Sub Macro1()
    cx = "A"
    Range(cx & "65536").End(xlUp).Select
        Range(Selection, Selection.End(xlUp)).Select
    RCT = Selection.Rows.Count
    For x = RCT To 2 Step -1
    stepmonth = DateSerial(Year(Cells(x - 1, cx)), (Month(Cells(x - 1, cx))), 1)
    If DateSerial(Year(Cells(x, cx)), Month(Cells(x, cx)) - 1, 1) <> stepmonth Then
    Rows(x).EntireRow.Insert
    Cells(x, cx) = DateSerial(Year(Cells(x + 1, cx)), (Month(Cells(x + 1, cx)) - 1), 1)
    x = x + 1
    End If
    Next
    End Sub
     
    
     
  3. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    turbodante

    Thanks, that was exactly what I needed!
    Only one problem though. The dates go for 55 years:

    1/02/2009
    1/07/2009
    1/07/2010
    .............
    1/07/2059
    1/07/2060
    1/07/2061
    1/07/2062


    When I run your code, it selects all the dates but will only finish adding months until year 2059:

    1/05/2059
    1/06/2059
    1/07/2059
    1/07/2060
    1/07/2061
    1/07/2062

    and comes up with a Run Time Error 13, Mismatch.

    Thanks
    Strybes
     
  4. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    I can not recreate this error with my sample data. Would you care to post up an example data set that gives you this error?
     
  5. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Hi turbodante

    Thanks for your assistance, it is really appreciated.
    I have attached a sample of data that I'm working with.
    As you can see the data has opening and closing balances with the totals for transactions for the financial year.
    Apart from having it include the months in between, I was then going to ask for help in how to do a caculation that would divide those transaction by the months in between the data (usually 12 except from the start date to the end of the 1st financial year). I was then going to have the opening balance = the previous months closing balance and have it sum up those monthly transactions to give me the next closing balance.
    You know if it works as the data already entered should have the previous months closing balance equaling the same.
    Is this possible.

    Thanks
    Strybes
     

    Attached Files:

  6. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    I can not recreate the error which you had previously described, but have modified the code the do what you had original requested re: inserting missing months...

    Code:
    Sub Macro1()
    
    Dim CX As String
    Dim topRow As Integer
    
    CX = "B"
    'Insert months
    Range(CX & "65536").End(xlUp).Select
        Range(Selection, Selection.End(xlUp)).Select
    topRow = Selection.Row() + 2
    RCT = Selection.Rows.Count
    For x = RCT To topRow Step -1
    stepmonth = DateSerial(Year(Cells(x - 1, CX)), (Month(Cells(x - 1, CX))), 1)
    If DateSerial(Year(Cells(x, CX)), Month(Cells(x, CX)) - 1, 1) <> stepmonth Then
    Rows(x).EntireRow.Insert
    Cells(x, CX) = DateSerial(Year(Cells(x + 1, CX)), (Month(Cells(x + 1, CX)) - 1), 1)
    x = x + 1
    End If
    Next
    End Sub
    
    
    
    Hold in there, I'll have a think about this part.
     
  7. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Seems that you're asking this on another thread. If the original query has been answered, please mark 'solved' and I shall try to assist with the next part on your other thread.
     
  8. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Thanks turbodante
    I started another thread as this one was getting a bit lengthy and the macro you provided works great.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/789527

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice