Need help with dynamic subtotal & row count

[psulion01]

The tablw below shows what I'm working with. The number of data rows will be dynamic from day to day, but the overall structure of the sheet should look the same.

How do I code:

1) The subtotals so they work whether I have 1 row of data or hundreds? The output will always be two rows below the last line of data.

2) How do I count the number of rows of data and display under the subtotal (i.e. on this sheet there were 5 rows). This will count all rows, not just those with unique data.

Thx in advance...you guys are GOOOOOD at this stuff! ****!

Daily Trade Summary Macro ver 3.1.xls
	A	B	C	D	E	F	G	H	I	J
1	9/28/2002
2
3	A/CNAME	A/C#	B/S	SYM	SHS	PRICE	DATE	ORDER#	PCs	LostPCs
4	SMITH		SELL	ABC	6,000		9/27/2002	1234	$300.00	$0.00
5	SMITH		SELL	DEF	12,000		9/27/2002	1235	$600.00	$0.00
6	SMITH		SELL	HIJ	500		9/27/2002	1236	$0.00	$25.00
7	SMITH		SELL	KLM	200		9/27/2002	1237	$0.00	$10.00
8	SMITH		SELL	NOP	10,500		9/27/2002	1238	$525.00	$0.00
9
10					29,200				$1,425.00	$35.00
11					5				5	5
data (16)

 

[Dave Patton]

You could consider built in functions.

If you require information by particular names etc consider Data Filter.

If you put your totals and counts above the
Data, The Function SubTotal can provide Sum, Max, Count etc of the Data. Subtotal works on visible data.

see Help in Excel for the various parameters.
This message was edited by Dave Patton on 2002-09-29 15:25

 

[psulion01]

Thanks Dave... SUBTOTAL should work but I need help coding it. Here's what I have (I don't know how to define the last row to be included in the subtotal. the last row will change every time I run the macro, so how do I code it to work dynamically?)

Here's what I have so far (for Column E only):


' Sum the columns

Dim all As Range
Set all = Range("E65536").End(xlUp)


Range("E65536").End(xlUp).Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,r4c5:all)"
Range("E65536").End(xlUp).Offset(1, 0).Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,r4c5:all)"

 

[Dave Patton]

On 2002-09-29 08:29, psulion01 wrote:
Thanks Dave... SUBTOTAL should work but I need help coding it. Here's what I have (I don't know how to define the last row to be included in the subtotal. the last row will change every time I run the macro, so how do I code it to work dynamically?)

Here's what I have so far (for Column E only):


' Sum the columns

Dim all As Range
Set all = Range("E65536").End(xlUp)


Range("E65536").End(xlUp).Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,r4c5:all)"
Range("E65536").End(xlUp).Offset(1, 0).Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,r4c5:all)"

I defer to others re VBA.

You could work out a solution using standard functions and then decide what you want to do with VBA.

1. Can you put your totals and counts at the top instead of at the bottom?

2. Can you create a Dynamic named Table
( search this forum for help if required).

3. Create Data filter and then review results.

4. Review various option parameters with Subtotal (see Help).
This message was edited by Dave Patton on 2002-09-29 10:48

 

[psulion01]

-bump-

Anyone handy with VBA care to offer some help?

 

[Dave Patton]

On 2002-09-29 14:43, psulion01 wrote:
-bump-

Anyone handy with VBA care to offer some help?

You did not advise
- if totals could go above data
- if you are using Autofilter
- why you require VBA

If you set up your database with Dynamic Range(s).

To use VBA to run a particular filter

Sub Macro1()

Selection.AutoFilter Field:=1, Criteria1:="Smith"
End Sub

 

[psulion01]

Dave,

I'm not sure what you're talking about with the autofilters.

No, I can't have the subtotals at the top. I need it just the way my example shows. The # of rows will be changing every time I create the sheet, so the macro must be able to dynamically place the subtotal 2 rows below the last row of data. One row below the subtotal I want a count of how many data rows were used to create the subtotal.

Is it clear now?

-- Better yet, is there a way to call a definied range in a formula? i.e. subtotal(2,range1) where range1 = a dynamic range.
This message was edited by psulion01 on 2002-09-29 19:55

 

[Tom Urtis]

Here's a macro that might do what you want.

It is redundant on purpose (the same two functions on 3 separate columns) for 2 reasons:

(1) It gives you flexibility to modify the column references in case you add or delete columns.

(2) There is different formatting (currency and non-currency) in your data so this code reflects that in the calculated Sum value display.

Here's the macro:

Sub KalckIt()

'Column E, SHS
With Range("E4").End(xlDown).Offset(2, 0)
.Value = WorksheetFunction.Sum(Range("E4", Range("E4").End(xlDown)))
.NumberFormat = "#,##0"
.Font.Bold = True
End With
With Range("E4").End(xlDown).Offset(3, 0)
.Value = WorksheetFunction.CountA(Range("E4", Range("E4").End(xlDown)))
.NumberFormat = "#,##0"
.Font.Bold = True
End With

'Column I, PCs
With Range("I4").End(xlDown).Offset(2, 0)
.Value = WorksheetFunction.Sum(Range("I4", Range("I4").End(xlDown)))
.NumberFormat = "$#,##0.00"
.Font.Bold = True
End With
With Range("I4").End(xlDown).Offset(3, 0)
.Value = WorksheetFunction.CountA(Range("I4", Range("I4").End(xlDown)))
.NumberFormat = "#,##0"
.Font.Bold = True
End With

'Column J, Lost PCs
With Range("J4").End(xlDown).Offset(2, 0)
.Value = WorksheetFunction.Sum(Range("J4", Range("J4").End(xlDown)))
.NumberFormat = "$#,##0.00"
.Font.Bold = True
End With
With Range("J4").End(xlDown).Offset(3, 0)
.Value = WorksheetFunction.CountA(Range("J4", Range("J4").End(xlDown)))
.NumberFormat = "#,##0"
.Font.Bold = True
End With

End Sub