Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUBTOTAL - TJ
I'm trying to create an automated way to subtotal. The following code will
subtotal if I have 3 rows of $ amounts in column D & F. However in my quote I may have any number of $ amounts to total from 1 - 100. How can I make the string intelligent enough to subtotatl those amounts with any number of rows populated. Example: (My quotes may be several grouping of items that I will sub total based on bid items) qty description unit price total price unit cost total unit cost Bid Item #1 5 45 deg bend $1.00 $5.00 $0.50 $2.50 5 90 deg bend $1.00 $5.00 $0.50 $2.50 10 22 deg bend $2.00 $20.00 $1.00 $10.00 subtotal $30.00 $15.00 Bid Item #2 5 tee $1.00 $5.00 $0.50 $2.50 5 coupling $1.00 $5.00 $0.50 $2.50 10 female adpt $2.00 $20.00 $1.00 $10.00 10 male adpt $2.00 $20.00 $1.00 $10.00 subtotal $50.00 $25.00 Bid Total $80.00 $40.00 again the idea is that I may have 1 or Many rows of amounts to total. The bellow code works if I simply have only 3 rows of amounts to total. I will always have a empty row between my column descriptions or my last subtotal and a empty row between my items and the subtotal. Sub SubTotal() Set objSelection = Selection Range("C" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "SUBTOTAL" Range("E" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Range("H" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" End Sub Then the last thing I would like to do in a seperate macro would be to total all of the subtotal's within the quote to give a complete bid total. So I need a code that searches my quotes for all the Total Price Totals and all the Total Cost totals and give me the totals as a "Grand Total" Ref the example above. Again I usually put 4 empty rows between my last Subtotal and My Grand Total. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUBTOTAL - TJ
TJ: Have you looked at the Data-Subtotals Menu item? It seems to me that it will do exactly what you want. HTH, Gary TJ Wrote: I'm trying to create an automated way to subtotal. The following code will subtotal if I have 3 rows of $ amounts in column D & F. However in my quote I may have any number of $ amounts to total from 1 - 100. How can I make the string intelligent enough to subtotatl those amounts with any number of rows populated. Example: (My quotes may be several grouping of items that I will sub total based on bid items) qty description unit price total price unit cost total unit cost Bid Item #1 5 45 deg bend $1.00 $5.00 $0.50 $2.50 5 90 deg bend $1.00 $5.00 $0.50 $2.50 10 22 deg bend $2.00 $20.00 $1.00 $10.00 subtotal $30.00 $15.00 Bid Item #2 5 tee $1.00 $5.00 $0.50 $2.50 5 coupling $1.00 $5.00 $0.50 $2.50 10 female adpt $2.00 $20.00 $1.00 $10.00 10 male adpt $2.00 $20.00 $1.00 $10.00 subtotal $50.00 $25.00 Bid Total $80.00 $40.00 again the idea is that I may have 1 or Many rows of amounts to total. The bellow code works if I simply have only 3 rows of amounts to total. I will always have a empty row between my column descriptions or my last subtotal and a empty row between my items and the subtotal. Sub SubTotal() Set objSelection = Selection Range("C" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "SUBTOTAL" Range("E" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Range("H" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" End Sub Then the last thing I would like to do in a seperate macro would be to total all of the subtotal's within the quote to give a complete bid total. So I need a code that searches my quotes for all the Total Price Totals and all the Total Cost totals and give me the totals as a "Grand Total" Ref the example above. Again I usually put 4 empty rows between my last Subtotal and My Grand Total. Thanks! -- GaryE Posted from - http://www.officehelp.in |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUBTOTAL - TJ
Can you give an example of it in a macro form? I'd like to be able to invoke
this subtotal with a ctrl s "GaryE" wrote: TJ: Have you looked at the Data-Subtotals Menu item? It seems to me that it will do exactly what you want. HTH, Gary TJ Wrote: I'm trying to create an automated way to subtotal. The following code will subtotal if I have 3 rows of $ amounts in column D & F. However in my quote I may have any number of $ amounts to total from 1 - 100. How can I make the string intelligent enough to subtotatl those amounts with any number of rows populated. Example: (My quotes may be several grouping of items that I will sub total based on bid items) qty description unit price total price unit cost total unit cost Bid Item #1 5 45 deg bend $1.00 $5.00 $0.50 $2.50 5 90 deg bend $1.00 $5.00 $0.50 $2.50 10 22 deg bend $2.00 $20.00 $1.00 $10.00 subtotal $30.00 $15.00 Bid Item #2 5 tee $1.00 $5.00 $0.50 $2.50 5 coupling $1.00 $5.00 $0.50 $2.50 10 female adpt $2.00 $20.00 $1.00 $10.00 10 male adpt $2.00 $20.00 $1.00 $10.00 subtotal $50.00 $25.00 Bid Total $80.00 $40.00 again the idea is that I may have 1 or Many rows of amounts to total. The bellow code works if I simply have only 3 rows of amounts to total. I will always have a empty row between my column descriptions or my last subtotal and a empty row between my items and the subtotal. Sub SubTotal() Set objSelection = Selection Range("C" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "SUBTOTAL" Range("E" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Range("H" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" End Sub Then the last thing I would like to do in a seperate macro would be to total all of the subtotal's within the quote to give a complete bid total. So I need a code that searches my quotes for all the Total Price Totals and all the Total Cost totals and give me the totals as a "Grand Total" Ref the example above. Again I usually put 4 empty rows between my last Subtotal and My Grand Total. Thanks! -- GaryE Posted from - http://www.officehelp.in |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUBTOTAL - TJ
And Subtotal the Grand Total with a ctrl g
"TJ" wrote: Can you give an example of it in a macro form? I'd like to be able to invoke this subtotal with a ctrl s "GaryE" wrote: TJ: Have you looked at the Data-Subtotals Menu item? It seems to me that it will do exactly what you want. HTH, Gary TJ Wrote: I'm trying to create an automated way to subtotal. The following code will subtotal if I have 3 rows of $ amounts in column D & F. However in my quote I may have any number of $ amounts to total from 1 - 100. How can I make the string intelligent enough to subtotatl those amounts with any number of rows populated. Example: (My quotes may be several grouping of items that I will sub total based on bid items) qty description unit price total price unit cost total unit cost Bid Item #1 5 45 deg bend $1.00 $5.00 $0.50 $2.50 5 90 deg bend $1.00 $5.00 $0.50 $2.50 10 22 deg bend $2.00 $20.00 $1.00 $10.00 subtotal $30.00 $15.00 Bid Item #2 5 tee $1.00 $5.00 $0.50 $2.50 5 coupling $1.00 $5.00 $0.50 $2.50 10 female adpt $2.00 $20.00 $1.00 $10.00 10 male adpt $2.00 $20.00 $1.00 $10.00 subtotal $50.00 $25.00 Bid Total $80.00 $40.00 again the idea is that I may have 1 or Many rows of amounts to total. The bellow code works if I simply have only 3 rows of amounts to total. I will always have a empty row between my column descriptions or my last subtotal and a empty row between my items and the subtotal. Sub SubTotal() Set objSelection = Selection Range("C" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "SUBTOTAL" Range("E" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Range("H" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" End Sub Then the last thing I would like to do in a seperate macro would be to total all of the subtotal's within the quote to give a complete bid total. So I need a code that searches my quotes for all the Total Price Totals and all the Total Cost totals and give me the totals as a "Grand Total" Ref the example above. Again I usually put 4 empty rows between my last Subtotal and My Grand Total. Thanks! -- GaryE Posted from - http://www.officehelp.in |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUBTOTAL - TJ
TJ: I don't know off the top of my head. I've never tried to do subtotals in a macro. I imagine it can be done. I would first figure out how to get the subtotal menu item to do what you want. Then turn on macro recording and click away. HTH, Gary TJ Wrote: And Subtotal the Grand Total with a ctrl g "TJ" wrote: Can you give an example of it in a macro form? I'd like to be able to invoke this subtotal with a ctrl s "GaryE" wrote: TJ: Have you looked at the Data-Subtotals Menu item? It seems to me that it will do exactly what you want. HTH, Gary TJ Wrote: I'm trying to create an automated way to subtotal. The following code will subtotal if I have 3 rows of $ amounts in column D & F. However in my quote I may have any number of $ amounts to total from 1 - 100. How can I make the string intelligent enough to subtotatl those amounts with any number of rows populated. Example: (My quotes may be several grouping of items that I will sub total based on bid items) qty description unit price total price unit cost total unit cost Bid Item #1 5 45 deg bend $1.00 $5.00 $0.50 $2.50 5 90 deg bend $1.00 $5.00 $0.50 $2.50 10 22 deg bend $2.00 $20.00 $1.00 $10.00 subtotal $30.00 $15.00 Bid Item #2 5 tee $1.00 $5.00 $0.50 $2.50 5 coupling $1.00 $5.00 $0.50 $2.50 10 female adpt $2.00 $20.00 $1.00 $10.00 10 male adpt $2.00 $20.00 $1.00 $10.00 subtotal $50.00 $25.00 Bid Total $80.00 $40.00 again the idea is that I may have 1 or Many rows of amounts to total. The bellow code works if I simply have only 3 rows of amounts to total. I will always have a empty row between my column descriptions or my last subtotal and a empty row between my items and the subtotal. Sub SubTotal() Set objSelection = Selection Range("C" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "SUBTOTAL" Range("E" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" Range("H" & objSelection.Row).Select ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)" End Sub Then the last thing I would like to do in a seperate macro would be to total all of the subtotal's within the quote to give a complete bid total. So I need a code that searches my quotes for all the Total Price Totals and all the Total Cost totals and give me the totals as a "Grand Total" Ref the example above. Again I usually put 4 empty rows between my last Subtotal and My Grand Total. Thanks! -- GaryE Posted from - http://www.officehelp.in -- GaryE Posted from - http://www.officehelp.in |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
subtotal nesting errors new this month | Excel Worksheet Functions | |||
subtotal a range of cells on a different worksheet | Excel Worksheet Functions | |||
Subtotal vs Pivot table - or best way | Excel Discussion (Misc queries) | |||
Subtotal Bug in Excel 2003 | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |