Hi, I'm looking for a macro that will insert and copy rows dependant on certain criteria:

Column 4 cannot exceed 50 days, so I need a macro that looks at the value in Col 4 and if more than 50 will calculate the # of additional rows required and copy and insert the data. For instance if D were 200, the macro would copy and insert 3 more lines (total of 4 rows) and the value in ALL rows Col D would be 50. If D were 201, 4 rows would be copied and inserted (total of 5 rows) 4 of those would have a value of 50 and the last row a value of 1. In addition, the date would DECREASE by one day for inserted row. So in the first example row 1 would have the dates as 31/12/2013, 30/12/2013, 29/12/2013, 28/12/2013 in the for rows.

Before Macro:

Emp# Name End_Date Days

23 James, Jim 12/31/2013 15

123456 Smith, John 12/31/2013 50

234564 Jones, Cathy 12/31/2013 165

45824 Doe, Manny 12/31/2013 322After macro:

Emp# Name End_Date Days

23 James, Jim 12/31/2013 15

123456 Smith, John 12/31/2013 50

234564 Jones, Cathy 12/31/2013 50

234564 Jones, Cathy 12/30/2013 50

234564 Jones, Cathy 12/29/2013 50

234564 Jones, Cathy 12/28/2013 15

45824 Doe, Manny 12/31/2013 50

45824 Doe, Manny 12/30/2013 50

45824 Doe, Manny 12/29/2013 50

45824 Doe, Manny 12/28/2013 50

45824 Doe, Manny 12/27/2013 50

45824 Doe, Manny 12/26/2013 50

45824 Doe, Manny 12/25/2013 22Thanks for looking at this

This is certainly doable, but I won't have access to a computer with Excel for a couple of days. If no one else offers a solution by the time I get back to civilization, I'll work on this then.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

First, a posting tip: Before posting any more example data in this forum, please click on the

blue lineat the end of this post and read the instructions on how to format example data so that it is easier for us to read. Thanks!As for your question, I pretty sure that this macro does what you want it to do.

The code is based on the assumption that your orginal data is in Sheet1, Columns A:D.

As written, the code writes the output to Sheet2, so if you have anything in Sheet2,

it is going to be overwritten. You can either change the code to write the data to a different sheet, or you can insert a blank Sheet2.Let me know if you have any questions.

Sub Fiftymax() 'Clear Sheet2, Copy Row 1 form Sheet1 Sheets(2).Cells.ClearContents Sheets(1).Rows(1).EntireRow.Copy _ Destination:=Sheets(2).Range("A1") 'Determine last row with data in Sheet1 lastRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 'Loop through data in Sheet1 For Each numDays In Sheets(1).Range("D2:D" & lastRow) 'If Days ,= 50, just copy Row to next blank Row in Sheet2 If numDays <= 50 Then nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1 numDays.EntireRow.Copy _ Destination:=Sheets(2).Range("A" & nxtRow) Else 'If Days > 50, Determine how many new rows will be needed numRows = Int(numDays / 50) + 1 'Copy Row to next blank row in Sheet2 multiple times For newRow = 1 To numRows nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1 numDays.EntireRow.Copy _ Destination:=Sheets(2).Range("A" & nxtRow) 'Set Columns D to 50 Sheets(2).Range("D" & nxtRow) = 50 If newRow > 1 Then 'Decrement Date Afer first row is copied Sheets(2).Range("C" & nxtRow) = _ Sheets(2).Range("C" & nxtRow - 1) - 1 'Determine remainder Days for final copied row If newRow = numRows Then Sheets(2).Range("D" & nxtRow) = numDays Mod 50 End If End If Next End If Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

That is PERFECT! This will save hours if not days of tedious cutting and pasting. Thanks so much - and I'll be sure to follow your psoting tips in the future:-)

B

Uh oh.... I didn't anticipate Col 4 not having only whole numbers so while the MOD function works on the whole numbers it is rounding any fractional remainders to whole. i.e.someone with 110.5 days should be broken down to 50, 50 and 10.5, but the MOD returns 11. Suggestions/ideas?

Either I never knew this, or I simply forgot it: In Excel MOD(110.5, 50) is a floating point operation and will return 10.5

In VBA, 110.5 Mod 50 is an integer operation and the 110.5 will be rounded to 110 before the division takes place, thus you will get 10. 110.6 will be rounded to 111 and you'll get 11.

One way to solve this is to force VBA to use the Excel MOD function instead of the VBA Mod operator.

Replace this:

numDays Mod 50with this:

Evaluate("MOD(" & numDays & ",50)")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Once again, AWESOME!. I did find the evaluate function but not being familiar with VBA syntax couldn't get it to work. But I'm learning :-)

Thanks again for help.

Ask Your Question

Weekly Poll