How do I break down a number by multiples in Excel?
 

I am using Excel 2003

I have a worksheet that allows me to QC the items that we fabricate and keep
an accurate count when they are loaded onto a truck. The "quantity" column
can have very high numbers which do not allow any room for the individual
piece count in the other columns. Plus, it can cause errors for multiple
deliveries on different trucks.

Is there a way to type in a quanty of 48 and have excel break it down to
four rows of 10 and one row of 8? I would also need the information on the
row that "48" was enterd on and have it copied to the other rows of 10 and 8.
I'm looking for a maximum of 10 for every line item that is entered or copied
to a worksheet.

For example:

NAME QTY NOTES
M165 48 TS6x6x1/2

NAME QTY NOTES
M165 10 TS6x6x1/2
M165 10 TS6x6x1/2
M165 10 TS6x6x1/2
M165 10 TS6x6x1/2
M165 8 TS6x6x1/2

Can this be done?

How do I break down a number by multiples in Excel?
 

Brian,

Assuming that your three columns are A, B, and C, copy the code below, right-click on the sheet
tab, select "view code", and paste the code in the window that appears. When you fill in your data,
fill in columns A and C first, then fill in the number in column B. It won't work automatically for
copied cells (where you copy all three in at the same time) until you select the cell in column B,
press F2, and press Enter.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myThres As Integer
Dim myVal As Integer

myThres = 10

If Target.Cells.Count < 1 Then Exit Sub
If Target.Column < 2 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub

On Error GoTo ErrHandler

myVal = Target.Value
Application.EnableEvents = False

For i = 0 To Int(Target.Value / myThres)
Target.EntireRow.Copy Target(i + 1).EntireRow
Target(i + 1).Value = Application.Min(myThres, myVal - myThres * i)
Next i

ErrHandler:
Application.EnableEvents = True

End Sub



"brian_sampson" wrote in message
...
I am using Excel 2003

I have a worksheet that allows me to QC the items that we fabricate and keep
an accurate count when they are loaded onto a truck. The "quantity" column
can have very high numbers which do not allow any room for the individual
piece count in the other columns. Plus, it can cause errors for multiple
deliveries on different trucks.

Is there a way to type in a quanty of 48 and have excel break it down to
four rows of 10 and one row of 8? I would also need the information on the
row that "48" was enterd on and have it copied to the other rows of 10 and 8.
I'm looking for a maximum of 10 for every line item that is entered or copied
to a worksheet.

For example:

NAME QTY NOTES
M165 48 TS6x6x1/2

NAME QTY NOTES
M165 10 TS6x6x1/2
M165 10 TS6x6x1/2
M165 10 TS6x6x1/2
M165 10 TS6x6x1/2
M165 8 TS6x6x1/2

Can this be done?

How do I break down a number by multiples in Excel?
 

small mod to make it wait until column 3 has something to copy

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myThres As Integer
Dim myVal As Integer

myThres = 10

If Target.Cells.Count < 1 Then Exit Sub
If Target.Column < 3 Then Exit Sub
If Not IsNumeric(Target.Offset(, -1)) Then Exit Sub

On Error GoTo ErrHandler

myVal = Target.Offset(, -1)
Application.EnableEvents = False

For i = 0 To Int(Target.Offset(, -1) / myThres)
Target.EntireRow.Copy Target(i + 1).EntireRow
Target(i + 1).Offset(, -1) = Application.Min(myThres, myVal - myThres * i)
Next i

ErrHandler:
Application.EnableEvents = True

End Sub

--
Don Guillett
SalesAid Software

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Brian,

Assuming that your three columns are A, B, and C, copy the code below,
right-click on the sheet tab, select "view code", and paste the code in
the window that appears. When you fill in your data, fill in columns A
and C first, then fill in the number in column B. It won't work
automatically for copied cells (where you copy all three in at the same
time) until you select the cell in column B, press F2, and press Enter.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myThres As Integer
Dim myVal As Integer

myThres = 10

If Target.Cells.Count < 1 Then Exit Sub
If Target.Column < 2 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub

On Error GoTo ErrHandler

myVal = Target.Value
Application.EnableEvents = False

For i = 0 To Int(Target.Value / myThres)
Target.EntireRow.Copy Target(i + 1).EntireRow
Target(i + 1).Value = Application.Min(myThres, myVal - myThres * i)
Next i

ErrHandler:
Application.EnableEvents = True

End Sub



"brian_sampson" wrote in message
...
I am using Excel 2003

I have a worksheet that allows me to QC the items that we fabricate and
keep
an accurate count when they are loaded onto a truck. The "quantity"
column
can have very high numbers which do not allow any room for the individual
piece count in the other columns. Plus, it can cause errors for multiple
deliveries on different trucks.

Is there a way to type in a quanty of 48 and have excel break it down to
four rows of 10 and one row of 8? I would also need the information on
the
row that "48" was enterd on and have it copied to the other rows of 10
and 8.
I'm looking for a maximum of 10 for every line item that is entered or
copied
to a worksheet.

For example:

NAME QTY NOTES
M165 48 TS6x6x1/2

NAME QTY NOTES
M165 10 TS6x6x1/2
M165 10 TS6x6x1/2
M165 10 TS6x6x1/2
M165 10 TS6x6x1/2
M165 8 TS6x6x1/2

Can this be done?