SUBTOTAL - TJ

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

 

[GaryE]

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 cod
will
subtotal if I have 3 rows of $ amounts in column D & F. However in m
quote
I may have any number of $ amounts to total from 1 - 100. How can
make the
string intelligent enough to subtotatl those amounts with any number o
rows
populated. Example: (My quotes may be several grouping of items that
will
sub total based on bid items)

qty description unit price total price unit cost tota
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.
will
always have a empty row between my column descriptions or my las
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 t
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 an
all
the Total Cost totals and give me the totals as a "Grand Total" Re
the
example above. Again I usually put 4 empty rows between my las
Subtotal and
My Grand Total.

Thanks

 

[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

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!

 

[TJ]

And Subtotal the Grand Total with a ctrl g

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

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!

 

[GaryE]

TJ:

I don't know off the top of my head. I've never tried to do subtotal
in a macro. I imagine it can be done. I would first figure out how t
get the subtotal menu item to do what you want. Then turn on macr
recording and click away.

HTH,
Gary

And Subtotal the Grand Total with a ctrl g

Can you give an example of it in a macro form? I'd like to be abl to invoke
this subtotal with a ctrl s

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 followin code
will
subtotal if I have 3 rows of $ amounts in column D & F. Howeve in my
quote
I may have any number of $ amounts to total from 1 - 100. Ho can I
make the
string intelligent enough to subtotatl those amounts with an number of
rows
populated. Example: (My quotes may be several grouping of item 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 t total.
The
bellow code works if I simply have only 3 rows of amounts t total. I
will
always have a empty row between my column descriptions or m 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 woul be to
total
all of the subtotal's within the quote to give a complete bi total.
So I
need a code that searches my quotes for all the Total Pric 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!