Dynamically adding rows to a spreadsheet based on number of month difference between start date and end date

[pmvidwans]

Hello,

I am working on a spreadsheet which has column A Start Date, column B End Date and column C Cost/Value. I need to distribute the cost across the number of months evently.

Example"

Column A | Column B | Column C
1-Jan-2014 31-Dec-2014 5000
2-Feb-2014 19-Feb-2014 2000
3-Mar-2014 1-June-2014 7000

Expected Result

Column A | Column B | Column C
1 Jan 2014 31-Dec-2014 =5000/12
1 Jan 2014 31-Dec-2014 =5000/12
1 Jan 2014 31-Dec-2014 =5000/12
1 Jan 2014 31-Dec-2014 =5000/12
1 Jan 2014 31-Dec-2014 =5000/12
1 Jan 2014 31-Dec-2014 =5000/12
1 Jan 2014 31-Dec-2014 =5000/12
1 Jan 2014 31-Dec-2014 =5000/12
1 Jan 2014 31-Dec-2014 =5000/12
1 Jan 2014 31-Dec-2014 =5000/12
1 Jan 2014 31-Dec-2014 =5000/12
1 Jan 2014 31-Dec-2014 =5000/12
2-Feb-2014 19-Feb-2014 =2000/1
3-Mar-2014 1-June-2014 =7000/4
3-Mar-2014 1-June-2014 =7000/4
3-Mar-2014 1-June-2014 =7000/4


The division is based on the number of month difference between the start date and end date.

Please suggest.

Thanks in Advance...

 

[hippiehacker]

maybe this?

Sub pmvidwans()

Set ws = ActiveSheet
Sheets.Add.Name = "Test"

For i = 1 To ws.Range("A" & Rows.Count).End(xlUp).Row

m1 = Format(ws.Cells(i, 1).Value, "mm")
m2 = Format(ws.Cells(i, 2).Value, "mm")
diff = (m2 - m1) + 1

For j = 1 To diff
 k = k + 1
  ActiveSheet.Cells(k, 1).Value = ws.Cells(i, 1).Value
  ActiveSheet.Cells(k, 2).Value = ws.Cells(i, 2).Value
  ActiveSheet.Cells(k, 3).Value = ws.Cells(i, 3).Value / diff
Next j
Next i
End Sub

 

[pmvidwans]

Hello HH,

Thanks for the response.. but it seems to be looping in itself in the J loop.

Also it is not adding additional rows below the active row based on difference.

Again for more clarity the expected output is as specified below

[TABLE="width: 535"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Total[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]February[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]May[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]June[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]July[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]August[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]September[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]October[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]November[/TD]
[/TR]
[TR]
[TD]1-Jan-14[/TD]
[TD]31-Dec-14[/TD]
[TD]416.67[/TD]
[TD]December[/TD]
[/TR]
[TR]
[TD]2-Feb-14[/TD]
[TD]19-Feb-14[/TD]
[TD]2000[/TD]
[TD]February[/TD]
[/TR]
[TR]
[TD]3-Mar-14[/TD]
[TD]1-Jun-14[/TD]
[TD]1750[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD]3-Mar-14[/TD]
[TD]1-Jun-14[/TD]
[TD]1750[/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD]3-Mar-14[/TD]
[TD]1-Jun-14[/TD]
[TD]1750[/TD]
[TD]May[/TD]
[/TR]
[TR]
[TD]3-Mar-14[/TD]
[TD]1-Jun-14[/TD]
[TD]1750[/TD]
[TD]June[/TD]
[/TR]
</tbody>[/TABLE]

Hope this information helps...

Thanks in advance..

 

[hippiehacker]

this is exactly what I got as the result when using my code

1. the new worksheet "Test has been created?
2. has been data written to the new workbook
3. the active sheet in the beginning is the sheet with your data on it?

 

[pmvidwans]

My Apologies... the code is working perfectly fine.

In addition to that could you suggest how to get the exact month value in the last column ?

I tried adding this statement towards the end (within the j loop) but no help...

ActiveSheet.Cells(k, 4).Value = Format(j, "mmmm")

Please suggest..

Thanks

 

[hippiehacker]

Sub pmvidwans()

Set ws = ActiveSheet
Sheets.Add.Name = "Test"

For i = 1 To ws.Range("A" & Rows.Count).End(xlUp).Row

m1 = Format(ws.Cells(i, 1).Value, "mm")
m2 = Format(ws.Cells(i, 2).Value, "mm")
diff = (m2 - m1) + 1
n = m1
For j = 1 To diff
 k = k + 1
  ActiveSheet.Cells(k, 1).Value = ws.Cells(i, 1).Value
  ActiveSheet.Cells(k, 2).Value = ws.Cells(i, 2).Value
  ActiveSheet.Cells(k, 3).Value = ws.Cells(i, 3).Value / diff
  ActiveSheet.Cells(k, 4).Value = MonthName(n)
 n = n + 1
Next j
Next i
End Sub