software

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 amp; 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(quot;Cquot; amp; objSelection.Row).Select
ActiveCell.FormulaR1C1 = quot;SUBTOTALquot;
Range(quot;Equot; amp; objSelection.Row).Select
ActiveCell.FormulaR1C1 = quot;=SUM(R[-4]C:R[-1]C)quot;
Range(quot;Hquot; amp; objSelection.Row).Select
ActiveCell.FormulaR1C1 = quot;=SUM(R[-4]C:R[-1]C)quot;
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 quot;Grand Totalquot; Ref the
example above. Again I usually put 4 empty rows between my last Subtotal and
My Grand Total.

Thanks!


TJ:

Have you looked at the Data-gt;Subtotals Menu item?

It seems to me that it will do exactly what you want.

HTH,
Gary

TJ Wrote:
gt; I'm trying to create an automated way to subtotal. The following code
gt; will
gt; subtotal if I have 3 rows of $ amounts in column D amp; F. However in my
gt; quote
gt; I may have any number of $ amounts to total from 1 - 100. How can I
gt; make the
gt; string intelligent enough to subtotatl those amounts with any number of
gt; rows
gt; populated. Example: (My quotes may be several grouping of items that I
gt; will
gt; sub total based on bid items)
gt;
gt; qty description unit price total price unit cost total
gt; unit
gt; cost
gt;
gt; Bid Item #1
gt;
gt; 5 45 deg bend $1.00 $5.00 $0.50 $2.50
gt; 5 90 deg bend $1.00 $5.00 $0.50 $2.50
gt; 10 22 deg bend $2.00 $20.00 $1.00 $10.00
gt;
gt; subtotal $30.00
gt; $15.00
gt;
gt; Bid Item #2
gt;
gt; 5 tee $1.00 $5.00 $0.50
gt; $2.50
gt; 5 coupling $1.00 $5.00 $0.50
gt; $2.50
gt; 10 female adpt $2.00 $20.00 $1.00 $10.00
gt; 10 male adpt $2.00 $20.00 $1.00
gt; $10.00
gt;
gt; subtotal $50.00
gt; $25.00
gt;
gt;
gt;
gt; Bid Total $80.00
gt; $40.00
gt;
gt; again the idea is that I may have 1 or Many rows of amounts to total.
gt; The
gt; bellow code works if I simply have only 3 rows of amounts to total. I
gt; will
gt; always have a empty row between my column descriptions or my last
gt; subtotal
gt; and a empty row between my items and the subtotal.
gt;
gt; Sub SubTotal()
gt; Set objSelection = Selection
gt; Range(quot;Cquot; amp; objSelection.Row).Select
gt; ActiveCell.FormulaR1C1 = quot;SUBTOTALquot;
gt; Range(quot;Equot; amp; objSelection.Row).Select
gt; ActiveCell.FormulaR1C1 = quot;=SUM(R[-4]C:R[-1]C)quot;
gt; Range(quot;Hquot; amp; objSelection.Row).Select
gt; ActiveCell.FormulaR1C1 = quot;=SUM(R[-4]C:R[-1]C)quot;
gt; End Sub
gt;
gt; Then the last thing I would like to do in a seperate macro would be to
gt; total
gt; all of the subtotal's within the quote to give a complete bid total.
gt; So I
gt; need a code that searches my quotes for all the Total Price Totals and
gt; all
gt; the Total Cost totals and give me the totals as a quot;Grand Totalquot; Ref
gt; the
gt; example above. Again I usually put 4 empty rows between my last
gt; Subtotal and
gt; My Grand Total.
gt;
gt; Thanks!--
GaryE
Posted from - www.officehelp.inCan you give an example of it in a macro form? I'd like to be able to invoke
this subtotal with a ctrl s

quot;GaryEquot; wrote:

gt;
gt; TJ:
gt;
gt; Have you looked at the Data-gt;Subtotals Menu item?
gt;
gt; It seems to me that it will do exactly what you want.
gt;
gt; HTH,
gt; Gary
gt;
gt; TJ Wrote:
gt; gt; I'm trying to create an automated way to subtotal. The following code
gt; gt; will
gt; gt; subtotal if I have 3 rows of $ amounts in column D amp; F. However in my
gt; gt; quote
gt; gt; I may have any number of $ amounts to total from 1 - 100. How can I
gt; gt; make the
gt; gt; string intelligent enough to subtotatl those amounts with any number of
gt; gt; rows
gt; gt; populated. Example: (My quotes may be several grouping of items that I
gt; gt; will
gt; gt; sub total based on bid items)
gt; gt;
gt; gt; qty description unit price total price unit cost total
gt; gt; unit
gt; gt; cost
gt; gt;
gt; gt; Bid Item #1
gt; gt;
gt; gt; 5 45 deg bend $1.00 $5.00 $0.50 $2.50
gt; gt; 5 90 deg bend $1.00 $5.00 $0.50 $2.50
gt; gt; 10 22 deg bend $2.00 $20.00 $1.00 $10.00
gt; gt;
gt; gt; subtotal $30.00
gt; gt; $15.00
gt; gt;
gt; gt; Bid Item #2
gt; gt;
gt; gt; 5 tee $1.00 $5.00 $0.50
gt; gt; $2.50
gt; gt; 5 coupling $1.00 $5.00 $0.50
gt; gt; $2.50
gt; gt; 10 female adpt $2.00 $20.00 $1.00 $10.00
gt; gt; 10 male adpt $2.00 $20.00 $1.00
gt; gt; $10.00
gt; gt;
gt; gt; subtotal $50.00
gt; gt; $25.00
gt; gt;
gt; gt;
gt; gt;
gt; gt; Bid Total $80.00
gt; gt; $40.00
gt; gt;
gt; gt; again the idea is that I may have 1 or Many rows of amounts to total.
gt; gt; The
gt; gt; bellow code works if I simply have only 3 rows of amounts to total. I
gt; gt; will
gt; gt; always have a empty row between my column descriptions or my last
gt; gt; subtotal
gt; gt; and a empty row between my items and the subtotal.
gt; gt;
gt; gt; Sub SubTotal()
gt; gt; Set objSelection = Selection
gt; gt; Range(quot;Cquot; amp; objSelection.Row).Select
gt; gt; ActiveCell.FormulaR1C1 = quot;SUBTOTALquot;
gt; gt; Range(quot;Equot; amp; objSelection.Row).Select
gt; gt; ActiveCell.FormulaR1C1 = quot;=SUM(R[-4]C:R[-1]C)quot;
gt; gt; Range(quot;Hquot; amp; objSelection.Row).Select
gt; gt; ActiveCell.FormulaR1C1 = quot;=SUM(R[-4]C:R[-1]C)quot;
gt; gt; End Sub
gt; gt;
gt; gt; Then the last thing I would like to do in a seperate macro would be to
gt; gt; total
gt; gt; all of the subtotal's within the quote to give a complete bid total.
gt; gt; So I
gt; gt; need a code that searches my quotes for all the Total Price Totals and
gt; gt; all
gt; gt; the Total Cost totals and give me the totals as a quot;Grand Totalquot; Ref
gt; gt; the
gt; gt; example above. Again I usually put 4 empty rows between my last
gt; gt; Subtotal and
gt; gt; My Grand Total.
gt; gt;
gt; gt; Thanks!
gt;
gt;
gt; --
gt; GaryE
gt; Posted from - www.officehelp.in
gt;
gt;

And Subtotal the Grand Total with a ctrl g

quot;TJquot; wrote:

gt; Can you give an example of it in a macro form? I'd like to be able to invoke
gt; this subtotal with a ctrl s
gt;
gt; quot;GaryEquot; wrote:
gt;
gt; gt;
gt; gt; TJ:
gt; gt;
gt; gt; Have you looked at the Data-gt;Subtotals Menu item?
gt; gt;
gt; gt; It seems to me that it will do exactly what you want.
gt; gt;
gt; gt; HTH,
gt; gt; Gary
gt; gt;
gt; gt; TJ Wrote:
gt; gt; gt; I'm trying to create an automated way to subtotal. The following code
gt; gt; gt; will
gt; gt; gt; subtotal if I have 3 rows of $ amounts in column D amp; F. However in my
gt; gt; gt; quote
gt; gt; gt; I may have any number of $ amounts to total from 1 - 100. How can I
gt; gt; gt; make the
gt; gt; gt; string intelligent enough to subtotatl those amounts with any number of
gt; gt; gt; rows
gt; gt; gt; populated. Example: (My quotes may be several grouping of items that I
gt; gt; gt; will
gt; gt; gt; sub total based on bid items)
gt; gt; gt;
gt; gt; gt; qty description unit price total price unit cost total
gt; gt; gt; unit
gt; gt; gt; cost
gt; gt; gt;
gt; gt; gt; Bid Item #1
gt; gt; gt;
gt; gt; gt; 5 45 deg bend $1.00 $5.00 $0.50 $2.50
gt; gt; gt; 5 90 deg bend $1.00 $5.00 $0.50 $2.50
gt; gt; gt; 10 22 deg bend $2.00 $20.00 $1.00 $10.00
gt; gt; gt;
gt; gt; gt; subtotal $30.00
gt; gt; gt; $15.00
gt; gt; gt;
gt; gt; gt; Bid Item #2
gt; gt; gt;
gt; gt; gt; 5 tee $1.00 $5.00 $0.50
gt; gt; gt; $2.50
gt; gt; gt; 5 coupling $1.00 $5.00 $0.50
gt; gt; gt; $2.50
gt; gt; gt; 10 female adpt $2.00 $20.00 $1.00 $10.00
gt; gt; gt; 10 male adpt $2.00 $20.00 $1.00
gt; gt; gt; $10.00
gt; gt; gt;
gt; gt; gt; subtotal $50.00
gt; gt; gt; $25.00
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Bid Total $80.00
gt; gt; gt; $40.00
gt; gt; gt;
gt; gt; gt; again the idea is that I may have 1 or Many rows of amounts to total.
gt; gt; gt; The
gt; gt; gt; bellow code works if I simply have only 3 rows of amounts to total. I
gt; gt; gt; will
gt; gt; gt; always have a empty row between my column descriptions or my last
gt; gt; gt; subtotal
gt; gt; gt; and a empty row between my items and the subtotal.
gt; gt; gt;
gt; gt; gt; Sub SubTotal()
gt; gt; gt; Set objSelection = Selection
gt; gt; gt; Range(quot;Cquot; amp; objSelection.Row).Select
gt; gt; gt; ActiveCell.FormulaR1C1 = quot;SUBTOTALquot;
gt; gt; gt; Range(quot;Equot; amp; objSelection.Row).Select
gt; gt; gt; ActiveCell.FormulaR1C1 = quot;=SUM(R[-4]C:R[-1]C)quot;
gt; gt; gt; Range(quot;Hquot; amp; objSelection.Row).Select
gt; gt; gt; ActiveCell.FormulaR1C1 = quot;=SUM(R[-4]C:R[-1]C)quot;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; Then the last thing I would like to do in a seperate macro would be to
gt; gt; gt; total
gt; gt; gt; all of the subtotal's within the quote to give a complete bid total.
gt; gt; gt; So I
gt; gt; gt; need a code that searches my quotes for all the Total Price Totals and
gt; gt; gt; all
gt; gt; gt; the Total Cost totals and give me the totals as a quot;Grand Totalquot; Ref
gt; gt; gt; the
gt; gt; gt; example above. Again I usually put 4 empty rows between my last
gt; gt; gt; Subtotal and
gt; gt; gt; My Grand Total.
gt; gt; gt;
gt; gt; gt; Thanks!
gt; gt;
gt; gt;
gt; gt; --
gt; gt; GaryE
gt; gt; Posted from - www.officehelp.in
gt; gt;
gt; gt;


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:
gt; And Subtotal the Grand Total with a ctrl g
gt;
gt; quot;TJquot; wrote:
gt;
gt; gt; Can you give an example of it in a macro form? I'd like to be able
gt; to invoke
gt; gt; this subtotal with a ctrl s
gt; gt;
gt; gt; quot;GaryEquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; TJ:
gt; gt; gt;
gt; gt; gt; Have you looked at the Data-gt;Subtotals Menu item?
gt; gt; gt;
gt; gt; gt; It seems to me that it will do exactly what you want.
gt; gt; gt;
gt; gt; gt; HTH,
gt; gt; gt; Gary
gt; gt; gt;
gt; gt; gt; TJ Wrote:
gt; gt; gt; gt; I'm trying to create an automated way to subtotal. The following
gt; code
gt; gt; gt; gt; will
gt; gt; gt; gt; subtotal if I have 3 rows of $ amounts in column D amp; F. However
gt; in my
gt; gt; gt; gt; quote
gt; gt; gt; gt; I may have any number of $ amounts to total from 1 - 100. How
gt; can I
gt; gt; gt; gt; make the
gt; gt; gt; gt; string intelligent enough to subtotatl those amounts with any
gt; number of
gt; gt; gt; gt; rows
gt; gt; gt; gt; populated. Example: (My quotes may be several grouping of items
gt; that I
gt; gt; gt; gt; will
gt; gt; gt; gt; sub total based on bid items)
gt; gt; gt; gt;
gt; gt; gt; gt; qty description unit price total price unit cost
gt; total
gt; gt; gt; gt; unit
gt; gt; gt; gt; cost
gt; gt; gt; gt;
gt; gt; gt; gt; Bid Item #1
gt; gt; gt; gt;
gt; gt; gt; gt; 5 45 deg bend $1.00 $5.00 $0.50
gt; $2.50
gt; gt; gt; gt; 5 90 deg bend $1.00 $5.00 $0.50
gt; $2.50
gt; gt; gt; gt; 10 22 deg bend $2.00 $20.00 $1.00
gt; $10.00
gt; gt; gt; gt;
gt; gt; gt; gt; subtotal $30.00
gt; gt; gt; gt; $15.00
gt; gt; gt; gt;
gt; gt; gt; gt; Bid Item #2
gt; gt; gt; gt;
gt; gt; gt; gt; 5 tee $1.00 $5.00 $0.50
gt; gt; gt; gt; $2.50
gt; gt; gt; gt; 5 coupling $1.00 $5.00 $0.50
gt; gt; gt; gt; $2.50
gt; gt; gt; gt; 10 female adpt $2.00 $20.00 $1.00
gt; $10.00
gt; gt; gt; gt; 10 male adpt $2.00 $20.00 $1.00
gt; gt; gt; gt; $10.00
gt; gt; gt; gt;
gt; gt; gt; gt; subtotal $50.00
gt; gt; gt; gt; $25.00
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Bid Total $80.00
gt; gt; gt; gt; $40.00
gt; gt; gt; gt;
gt; gt; gt; gt; again the idea is that I may have 1 or Many rows of amounts to
gt; total.
gt; gt; gt; gt; The
gt; gt; gt; gt; bellow code works if I simply have only 3 rows of amounts to
gt; total. I
gt; gt; gt; gt; will
gt; gt; gt; gt; always have a empty row between my column descriptions or my
gt; last
gt; gt; gt; gt; subtotal
gt; gt; gt; gt; and a empty row between my items and the subtotal.
gt; gt; gt; gt;
gt; gt; gt; gt; Sub SubTotal()
gt; gt; gt; gt; Set objSelection = Selection
gt; gt; gt; gt; Range(quot;Cquot; amp; objSelection.Row).Select
gt; gt; gt; gt; ActiveCell.FormulaR1C1 = quot;SUBTOTALquot;
gt; gt; gt; gt; Range(quot;Equot; amp; objSelection.Row).Select
gt; gt; gt; gt; ActiveCell.FormulaR1C1 = quot;=SUM(R[-4]C:R[-1]C)quot;
gt; gt; gt; gt; Range(quot;Hquot; amp; objSelection.Row).Select
gt; gt; gt; gt; ActiveCell.FormulaR1C1 = quot;=SUM(R[-4]C:R[-1]C)quot;
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; Then the last thing I would like to do in a seperate macro would
gt; be to
gt; gt; gt; gt; total
gt; gt; gt; gt; all of the subtotal's within the quote to give a complete bid
gt; total.
gt; gt; gt; gt; So I
gt; gt; gt; gt; need a code that searches my quotes for all the Total Price
gt; Totals and
gt; gt; gt; gt; all
gt; gt; gt; gt; the Total Cost totals and give me the totals as a quot;Grand Totalquot;
gt; Ref
gt; gt; gt; gt; the
gt; gt; gt; gt; example above. Again I usually put 4 empty rows between my last
gt; gt; gt; gt; Subtotal and
gt; gt; gt; gt; My Grand Total.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; GaryE
gt; gt; gt; Posted from - www.officehelp.in
gt; gt; gt;
gt; gt; gt;--
GaryE
Posted from - www.officehelp.in