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

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