Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Change number when you open the excel template | Excel Discussion (Misc queries) | |||
Access Excel Linked Text and Number Issues | Excel Discussion (Misc queries) | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) | |||
Can the number of times undo is used in Excel 2002 be increased? | Setting up and Configuration of Excel |