I have a customer sheet with 2-3 thousand rows, each with 24 columns.
At any time, a row may be split(new row) and the values in A thru D
(names) and from column K (due) copy/pasted to the new row, and a text
value added to column E to note it. I've managed to do that with a
macro, probably with many more lines than necessary.
I now need to split that dollar value in column K into THREE new rows
when I copy it (payments), what code do I add to get three equal
amounts in those three new rows? Is there a quicker way to do this
without the 40 lines of code I have now?

I would use the worksheet function floor. Because 3 doesn't evenly divide
two rows will get same value you want two rows to have the same value and one
row to be only offf by .01.

Sub splitdollar()

originaltotal = 15.62
Div3total = WorksheetFunction.Floor(originaltotal / 3, 0.01)
row1total = Div3total
Tworowtotal = originaltotal - Div3total
Div2total = WorksheetFunction. _
Floor((Tworowtotal) / 2, 0.01)
row2total = Div2total
row3total = Tworowtotal - Div2total

End Sub

" wrote:

I have a customer sheet with 2-3 thousand rows, each with 24 columns.
At any time, a row may be split(new row) and the values in A thru D
(names) and from column K (due) copy/pasted to the new row, and a text
value added to column E to note it. I've managed to do that with a
macro, probably with many more lines than necessary.
I now need to split that dollar value in column K into THREE new rows
when I copy it (payments), what code do I add to get three equal
amounts in those three new rows? Is there a quicker way to do this
without the 40 lines of code I have now?

since my original total varies by customer, how do I enter something
like active row cell K as the input? This is only needed for aprx 1
of every 100 customers.
I am trying to have a macro that will invoke adding three rows, split
cell K into three and place the partial amounts into the 'new' cell
K's. I can do that now with only one new row - more than that and I
get lost in VBA.


On Mon, 13 Aug 2007 04:22:01 -0700, Joel
wrote:

I would use the worksheet function floor. Because 3 doesn't evenly divide
two rows will get same value you want two rows to have the same value and one
row to be only offf by .01.

Sub splitdollar()

originaltotal = 15.62
Div3total = WorksheetFunction.Floor(originaltotal / 3, 0.01)
row1total = Div3total
Tworowtotal = originaltotal - Div3total
Div2total = WorksheetFunction. _
Floor((Tworowtotal) / 2, 0.01)
row2total = Div2total
row3total = Tworowtotal - Div2total

End Sub

" wrote:

I have a customer sheet with 2-3 thousand rows, each with 24 columns.
At any time, a row may be split(new row) and the values in A thru D
(names) and from column K (due) copy/pasted to the new row, and a text
value added to column E to note it. I've managed to do that with a
macro, probably with many more lines than necessary.
I now need to split that dollar value in column K into THREE new rows
when I copy it (payments), what code do I add to get three equal
amounts in those three new rows? Is there a quicker way to do this
without the 40 lines of code I have now?

You may just want to add one row three times. Or

rows($3:$5).insert

or

RowCount = 25
rows(RowCount & ":" & (RowCount + 2)).insert

"OzonedMan" wrote:

since my original total varies by customer, how do I enter something
like active row cell K as the input? This is only needed for aprx 1
of every 100 customers.
I am trying to have a macro that will invoke adding three rows, split
cell K into three and place the partial amounts into the 'new' cell
K's. I can do that now with only one new row - more than that and I
get lost in VBA.


On Mon, 13 Aug 2007 04:22:01 -0700, Joel
wrote:

I would use the worksheet function floor. Because 3 doesn't evenly divide
two rows will get same value you want two rows to have the same value and one
row to be only offf by .01.

Sub splitdollar()

originaltotal = 15.62
Div3total = WorksheetFunction.Floor(originaltotal / 3, 0.01)
row1total = Div3total
Tworowtotal = originaltotal - Div3total
Div2total = WorksheetFunction. _
Floor((Tworowtotal) / 2, 0.01)
row2total = Div2total
row3total = Tworowtotal - Div2total

End Sub

" wrote:

I have a customer sheet with 2-3 thousand rows, each with 24 columns.
At any time, a row may be split(new row) and the values in A thru D
(names) and from column K (due) copy/pasted to the new row, and a text
value added to column E to note it. I've managed to do that with a
macro, probably with many more lines than necessary.
I now need to split that dollar value in column K into THREE new rows
when I copy it (payments), what code do I add to get three equal
amounts in those three new rows? Is there a quicker way to do this
without the 40 lines of code I have now?