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!

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

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

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

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