Results 1 to 8 of 8

Thread: VBA or Macro to add rows between dates

  1. #1
    Neophyte Manf1976's Avatar
    Join Date
    Jun 2019
    Posts
    4
    Articles
    0
    Excel Version
    Excel 2013

    Exclamation VBA or Macro to add rows between dates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    HI, I have been beating my head against this problem for some time but haven't yet managed to find a solution. I have an extensive spread sheet of financial data that i am trying to transpose into a different format. I am sure a VBA script will do the job but i am very inexperienced in VBA coding and everything i have tired so far has failed.
    I am trying to make this (sample only):
    Index Start Date End Date Monthly Cost
    index 1 01/01/2015 25/04/2015 100
    index 2 02/04/2017 03/06/2017 120
    index 3 03/05/2013 01/08/2013 150

    Look like this:
    Index Start Date End Date Monthly Cost Billing Date
    index 1 15/01/2015 25/04/2015 50 15/01/2015
    index 1 15/01/2015 25/04/2015 100 01/02/2015
    index 1 15/01/2015 25/04/2015 100 01/03/2015
    index 1 15/01/2015 25/04/2015 33.30 01/04/2015
    index 2 10/04/2017 10/06/2017 80 10/04/2017
    index 2 10/04/2017 10/06/2017 120 01/05/2017
    index 2 10/04/2017 10/06/2017 40 01/06/2017
    index 3 05/05/2013 30/08/2013 125 05/05/2013
    index 3 05/05/2013 30/08/2013 150 01/06/2013
    index 3 05/05/2013 30/08/2013 150 01/07/2013
    index 3 05/05/2013 30/08/2013 150 01/08/2013

    I have other data in other columns that i would also need to copy down but it wouldn't need to change just stay aligned to the correct index (in case it makes a difference i am using Excel 2013).

    Any help would be much appreciated.
    Last edited by Manf1976; 2019-06-14 at 02:23 PM.

  2. #2
    Neophyte Manf1976's Avatar
    Join Date
    Jun 2019
    Posts
    4
    Articles
    0
    Excel Version
    Excel 2013
    Cant seem to edit so I would just like to add that i have also posted this question on the Tech Forums https://techcommunity.microsoft.com/.../690453#M32769 and i will happily spread the answers around if i find any.

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    768
    Articles
    0
    Excel Version
    Excel 2010 64bit
    I am trying to make this (sample only):
    .
    .
    Look like this:
    How do you turn 3 lines into 11 ?
    Any help would be much appreciated.
    The greatest help would come from you attaching an Excel file with enough data to show and understand what you're trying to do.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,585
    Articles
    0
    Excel Version
    365
    Are those start dates right in both tables?
    What's the relation between:
    1/1/2015 and 15/1/2015 (14 days later)
    2/4/2017 and 10/4/2017 (8 days later)
    3/5/2013 and 5/5/2013 (2 days later)
    ?

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,585
    Articles
    0
    Excel Version
    365
    From:
    Click image for larger version. 

Name:	2019-06-15_134846.jpg 
Views:	11 
Size:	6.8 KB 
ID:	9161
    I can get you:
    Click image for larger version. 

Name:	2019-06-15_134936.jpg 
Views:	15 
Size:	26.1 KB 
ID:	9162
    or:
    Click image for larger version. 

Name:	2019-06-15_134952.jpg 
Views:	12 
Size:	19.5 KB 
ID:	9163
    Or is neither of these right?

  6. #6
    Neophyte Manf1976's Avatar
    Join Date
    Jun 2019
    Posts
    4
    Articles
    0
    Excel Version
    Excel 2013
    Hi, that looks perfect, the first version that is.

    And no it looks like i may have made a mistake with the sample data in terms of index2's original dates. (I needed some simple dates to calculate the sample monthly cost values but forgot to edit the original to match.) I am sure that didn't make it any easier so many thanks for achieving something even with bad data.

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,585
    Articles
    0
    Excel Version
    365
    Code:
    Sub blah()
    Set Destn = Range("A20") 'this is where the first result will go.
    Set myRng = Range("A1").CurrentRegion.Resize(, 4) 'assumes the source table is at cell A1 and has column headers.
    Set myRng = Intersect(myRng, myRng.Offset(1))
    For Each rw In myRng.Rows
      SD = rw.Cells(2).Value
      ED = rw.Cells(3).Value
      MC = rw.Cells(4).Value
      ThisMonthStart = Application.WorksheetFunction.EoMonth(SD, -1) + 1
      ThisMonthEnd = Application.WorksheetFunction.EoMonth(SD, 0)
      Do
        BillPeriod = Application.Max(0, Application.Min(ED, ThisMonthEnd) - Application.Max(SD, ThisMonthStart) + 1)
        ThisMonthLength = Day(ThisMonthEnd)
        ThisMonthBill = MC * BillPeriod / ThisMonthLength
        Destn.Resize(, 5).Value = Array(rw.Cells(1).Value, SD, ED, ThisMonthBill, CDate(Application.Max(SD, ThisMonthStart)))
        Set Destn = Destn.Offset(1)
        ThisMonthStart = ThisMonthEnd + 1
        ThisMonthEnd = Application.WorksheetFunction.EoMonth(ThisMonthStart, 0)
      Loop Until ThisMonthStart > ED
    Next rw
    End Sub
    See button in attached.
    Attached Files Attached Files

  8. #8
    Neophyte Manf1976's Avatar
    Join Date
    Jun 2019
    Posts
    4
    Articles
    0
    Excel Version
    Excel 2013
    That is amazing. Does exactly what i needed. Thank you very very much.

Posting Permissions

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