Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Subtotal Macro
We estimate schematic building designs for architectural clients using Excel,
using a separate page for each category. Frequently, there are several subcategories for which a subtotal would be useful. I program in Access frequently, so am familiar with the basics of VBA, but would like help with Excel-specific syntax. The macro I'm trying to create, in pseudocode is: - Get the Address of the Cell from which the macro was invoked; this will be the first subtotal location, SubtotalAddress - The Cost is in column F, so the beginning of the subtotal range is Col. F, same row - Examine the Description in Column B in the next row - While Left(this cell's value,2) = " -", then it's still part of this category, so examine the next row's description - When kicked out of the loop, we're at a new category, so the end of the range is the previous cell - Assign @sum(BeginRange..EndRange) to the SubtotalAddress.Formula - Position SubtotalAddress to the next category - Loop until the value in the cost in Column F is null The expected output (subtotal in col. G) might look like: B C D E F G Partitions - Typical 6,720 LF 50.00 336,000 583,050 - Shaftwall 220 LF 90.00 19,800 - Perimeter Furring 17,620 SF 2.50 44,050 - Column Covers 168 EA 200.00 33,600 - Soffit 3,740 LF 40.00 149,600 Slab Penetrations 24 EA 250.00 6,000 6,000 Patching 300 EA 50.00 15,000 15,000 Door - SC Maple 108 EA 900.00 97,200 172,800 - Misc./Single 84 EA 900.00 75,600 Interior Glazing 1 LS - NIC 0 Floor Finish - Resinous 4,680 SF 8.50 39,780 151,640 - VCT @ Stairwells 2,760 SF 2.00 5,520 - Carpet/Typical 4,090 SY 26.00 106,340 Thanks for any and all assistance. Kevin Sprinkel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create Subtotal Macro
I'd use another column of formulas to define the category:
Insert a new column A. Headers in row 1. first row of data in row 2 in A3: =if(left(b3=" -",a2,b3) drag down. This should put the top category on each row. then you can do data|subtotals on that column. If you need a macro, you can record one when you do it manually. (including even adding the extra column and adding the formulas) Sprinks wrote: We estimate schematic building designs for architectural clients using Excel, using a separate page for each category. Frequently, there are several subcategories for which a subtotal would be useful. I program in Access frequently, so am familiar with the basics of VBA, but would like help with Excel-specific syntax. The macro I'm trying to create, in pseudocode is: - Get the Address of the Cell from which the macro was invoked; this will be the first subtotal location, SubtotalAddress - The Cost is in column F, so the beginning of the subtotal range is Col. F, same row - Examine the Description in Column B in the next row - While Left(this cell's value,2) = " -", then it's still part of this category, so examine the next row's description - When kicked out of the loop, we're at a new category, so the end of the range is the previous cell - Assign @sum(BeginRange..EndRange) to the SubtotalAddress.Formula - Position SubtotalAddress to the next category - Loop until the value in the cost in Column F is null The expected output (subtotal in col. G) might look like: B C D E F G Partitions - Typical 6,720 LF 50.00 336,000 583,050 - Shaftwall 220 LF 90.00 19,800 - Perimeter Furring 17,620 SF 2.50 44,050 - Column Covers 168 EA 200.00 33,600 - Soffit 3,740 LF 40.00 149,600 Slab Penetrations 24 EA 250.00 6,000 6,000 Patching 300 EA 50.00 15,000 15,000 Door - SC Maple 108 EA 900.00 97,200 172,800 - Misc./Single 84 EA 900.00 75,600 Interior Glazing 1 LS - NIC 0 Floor Finish - Resinous 4,680 SF 8.50 39,780 151,640 - VCT @ Stairwells 2,760 SF 2.00 5,520 - Carpet/Typical 4,090 SY 26.00 106,340 Thanks for any and all assistance. Kevin Sprinkel -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort macro, subtotal and add lines after subtotal | Excel Discussion (Misc queries) | |||
macro excel subtotal in subtotal | Excel Discussion (Misc queries) | |||
Create subtotal column in pivot table | Excel Discussion (Misc queries) | |||
not able to create subtotal summary report | Excel Worksheet Functions | |||
Create a subtotal | Excel Programming |