Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totalling then Averaging subject to Product Code change
I have a spreadsheet with the following data:
Sorted Alphabetic Product Codes (3-6 Alpha letters) in Column C starting at Row 4 - the last entry in Column C will always be blank Units (6 numeric numbers nnn,nnn format) in Column F also starting in Row 4 Value ($$$,$$$.cc format) in Column K also starting in Row 4 The main process occurs when a new Product Code occurs in Column C. When it does I need to put in the last row of the the previous Product Code the following: 1. total of the Units from its Column F into the the associated cell of Column N 2. total of the Value from its Column K into the the associated cell of Column O 3. divide the contents of the relative cell in O by the contents of the relative cell in N to calculate the average into the relative cell in P (fomat $$$.ccc). For example when the rows in Column C change from code ABCD to ABCE calculate the total of the units and value for ABCD and put their values into the associated cells in column N and O of that last row for ABCD - then divide the associated cell in O by the associated cell in N to get the average and put it into associated cell in P. If someone can help I would really like a macro to automate this process |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totalling then Averaging subject to Product Code change
I fully test this code so it should work if there is at least some data starting in row 4. Sub Makesubtotals() LastRow = Range("C" & Rows.Count).End(xlUp).Row Rows("4:" & LastRow).Sort _ header:=xlNo, _ key1:=Range("C4"), _ order1:=xlAscending RowCount = 4 StartRow = RowCount 'first row of the addion for each product code Do While Range("C" & RowCount) < "" If Range("C" & RowCount) < _ Range("C" & (RowCount + 1)) Then 'insert new row Rows(RowCount + 1).Insert 'make a formula to add the column Range("N" & (RowCount + 1)).Formula = _ "=Sum(F" & StartRow & ":F" & RowCount & ")" Range("O" & (RowCount + 1)).Formula = _ "=Sum(K" & StartRow & ":K" & RowCount & ")" Range("P" & (RowCount + 1)).Formula = _ "=O" & (RowCount + 1) & "/N" & (RowCount + 1) RowCount = RowCount + 2 StartRow = RowCount Else RowCount = RowCount + 1 End If Loop End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=177957 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totalling then Averaging subject to Product Code change
Thanks again Joel for that - still one small problem (example follows):
The first Product Code in Column C has the alpha letters AGK for 9 rows of data starting at Row 4. The next Product Code in Column C has the alpha letters AIO for 6 rows of data starting at Row 13 When I run the code the first Row for Product Code (AGK) always gets moved to the Row before the next Product Code AIO (ie Row 13) Then the code totals rows 1 to 8 as a Product Code and Row 13 as a new Product Code before moving on. It seems like a problem only after its worked out how many rows there are in the very first Product Code. From then on everything is fine. Thanks Chris "joel" wrote: I fully test this code so it should work if there is at least some data starting in row 4. Sub Makesubtotals() LastRow = Range("C" & Rows.Count).End(xlUp).Row Rows("4:" & LastRow).Sort _ header:=xlNo, _ key1:=Range("C4"), _ order1:=xlAscending RowCount = 4 StartRow = RowCount 'first row of the addion for each product code Do While Range("C" & RowCount) < "" If Range("C" & RowCount) < _ Range("C" & (RowCount + 1)) Then 'insert new row Rows(RowCount + 1).Insert 'make a formula to add the column Range("N" & (RowCount + 1)).Formula = _ "=Sum(F" & StartRow & ":F" & RowCount & ")" Range("O" & (RowCount + 1)).Formula = _ "=Sum(K" & StartRow & ":K" & RowCount & ")" Range("P" & (RowCount + 1)).Formula = _ "=O" & (RowCount + 1) & "/N" & (RowCount + 1) RowCount = RowCount + 2 StartRow = RowCount Else RowCount = RowCount + 1 End If Loop End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=177957 Microsoft Office Help . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totalling then Averaging subject to Product Code change
I'm thinking it something with the data and not the code since I ddi a good job of checking the code and have used this code lots of time in the past. Can you post the formulas that you have in cells N13, O13, P13. The formulas should read =Sum(F4:F12) =Sum(K4:K12) =O13/O12 You first posting said you data starts in row 4. Your second posting said you had 9 occurances of AGK (rows 4 to 12). the code would then add an empty row at row 13 and insert the formulas in the new row. The first row that starts counting is row for and the first row that get put into the formulas is the variable StartRow as shown in these two lines RowCount = 4 StartRow = RowCount -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=177957 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totalling then Averaging subject to Product Code change
Thanks joel for your patience here
The first code is called AGK (in C4) and despite there being 9 data rows for it the first inserted Total line for AGK is in row 12 (not 13) and the formulas in that line are as follows N12=SUM(F4:F11) O12=SUM(K4:K11) P12=O12/N12 The only problem with the whole thing is that the original last data row for AGK ( ie in row 12) gets moved down one row and treated as a new unique Product Code with its own Total row inserted after it. I stepped it through and watched it insert this last line then every Code after that was fine and in the right place "joel" wrote: I'm thinking it something with the data and not the code since I ddi a good job of checking the code and have used this code lots of time in the past. Can you post the formulas that you have in cells N13, O13, P13. The formulas should read =Sum(F4:F12) =Sum(K4:K12) =O13/O12 You first posting said you data starts in row 4. Your second posting said you had 9 occurances of AGK (rows 4 to 12). the code would then add an empty row at row 13 and insert the formulas in the new row. The first row that starts counting is row for and the first row that get put into the formulas is the variable StartRow as shown in these two lines RowCount = 4 StartRow = RowCount -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=177957 Microsoft Office Help . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totalling then Averaging subject to Product Code change
The data in C11 and C12 aren't exactly matching. this usually implies the is either a extra space in the string or some letters are capitalized. The easy thing to do is to copy C11 to C12 and run the code again. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=177957 Microsoft Office Help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totalling then Averaging subject to Product Code change
Thanks joel - I had a similar spreadsheet to this one and ran the macro fine
so I did as you suggested with a variation by dragging C4 all the way down through to C11 and it ran fine. Thanks again Chris "joel" wrote: The data in C11 and C12 aren't exactly matching. this usually implies the is either a extra space in the string or some letters are capitalized. The easy thing to do is to copy C11 to C12 and run the code again. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=177957 Microsoft Office Help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Product Code and Product Description setup | Excel Worksheet Functions | |||
VLOOKUP - TABLE_ARRAY SUBJECT TO CHANGE | Excel Discussion (Misc queries) | |||
Vlookup code product and to copy commentary with photo of the product in vba | Excel Programming | |||
VBA Code for Matching and totalling columns | Excel Worksheet Functions | |||
Code to Email with UNC in subject line | Excel Programming |