#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TJ
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
GaryE
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TJ
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TJ
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
GaryE
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
subtotal nesting errors new this month Greenebush Excel Worksheet Functions 4 March 17th 06 11:35 PM
subtotal a range of cells on a different worksheet cheryl Excel Worksheet Functions 0 November 2nd 05 09:37 PM
Subtotal vs Pivot table - or best way klafert Excel Discussion (Misc queries) 2 June 16th 05 06:29 PM
Subtotal Bug in Excel 2003 GON Excel Discussion (Misc queries) 2 May 10th 05 08:42 PM
Subtotal of Subtotal displays Grand Total in wrong row Thomas Born Excel Worksheet Functions 5 January 6th 05 02:46 PM


All times are GMT +1. The time now is 10:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"