VBA or Macro to add rows between dates

Manf1976

New member
Joined
Jun 14, 2019
Messages
4
Reaction score
0
Points
0
Excel Version(s)
Excel 2013
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):
IndexStart DateEnd DateMonthly Cost
index 101/01/201525/04/2015£100
index 202/04/201703/06/2017£120
index 303/05/201301/08/2013£150

Look like this:
IndexStart DateEnd DateMonthly CostBilling Date
index 115/01/201525/04/2015£5015/01/2015
index 115/01/201525/04/2015£10001/02/2015
index 115/01/201525/04/2015£10001/03/2015
index 115/01/201525/04/2015£33.3001/04/2015
index 210/04/201710/06/2017£8010/04/2017
index 210/04/201710/06/2017£12001/05/2017
index 210/04/201710/06/2017£4001/06/2017
index 305/05/201330/08/2013£12505/05/2013
index 305/05/201330/08/2013£15001/06/2013
index 305/05/201330/08/2013£15001/07/2013
index 305/05/201330/08/2013£15001/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:
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.
 
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)
?
 
From:
2019-06-15_134846.jpg
I can get you:
2019-06-15_134936.jpg
or:
2019-06-15_134952.jpg
Or is neither of these right?
 
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.
 
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.
 

Attachments

  • ExcelGuru10046.xlsm
    21 KB · Views: 10
That is amazing. Does exactly what i needed. Thank you very very much.
 
Back
Top