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

Start Date	End Date	Total	Month
1-Jan-14	31-Dec-14	416.67	January
1-Jan-14	31-Dec-14	416.67	February
1-Jan-14	31-Dec-14	416.67	March
1-Jan-14	31-Dec-14	416.67	April
1-Jan-14	31-Dec-14	416.67	May
1-Jan-14	31-Dec-14	416.67	June
1-Jan-14	31-Dec-14	416.67	July
1-Jan-14	31-Dec-14	416.67	August
1-Jan-14	31-Dec-14	416.67	September
1-Jan-14	31-Dec-14	416.67	October
1-Jan-14	31-Dec-14	416.67	November
1-Jan-14	31-Dec-14	416.67	December
2-Feb-14	19-Feb-14	2000	February
3-Mar-14	1-Jun-14	1750	March
3-Mar-14	1-Jun-14	1750	April
3-Mar-14	1-Jun-14	1750	May
3-Mar-14	1-Jun-14	1750	June

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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