SUBTOTAL - TJ

G

Guest

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!
 
G

Guest

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 said:
TJ:

Have you looked at the Data->Subtotals Menu item?

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

HTH,
Gary
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!
 
G

Guest

And Subtotal the Grand Total with a ctrl g

TJ said:
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 said:
TJ:

Have you looked at the Data->Subtotals Menu item?

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

HTH,
Gary
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top