Sum a Range dynamically

[sandylou]

I have a set I am trying to dynamically sum. For instance I know my column is varCol and the number of rows is varRow. This is what I am have coded:

'This is where I want the formula
Range(Cells((varNumRow + 1), varColNum), Cells((varNumRow + 1), varColNum)).Select
'I want to sum everything above that column to row 2, row one is headers
ActiveCell.FormulaR1C1 = "=SUM(R[-" & varNumRow & "]C:R[-1]C)"

and I get an error...any suggestions?

 

[xlbo]

Your formula looks sound - 2 suggestions:

"For instance I know my column is varCol "
"Range(Cells((varNumRow + 1), varColNum), "
check your variable name

Also - check what your variable equates to - see if it does return the correct number of rows that you want to sum Rgds
~Geoff~

 

[sandylou]

For some reason after I closed the Access application I noticed that the Excel application was not being released. So I ended the task(s) and then it worked. My problem is now this: I want to sum rows within the spreadsheet dyanmically. Here is what I have but what happens is that the first range of cells is the sum that is placed on each of the subsequent ranges. For example I want to sum A1:A15 and then I want to sum A17:A100. And here is the code I have:

Cells(varCurRow, 14).Activate
varFirstCell = ActiveCell.Address
'we need to put the values in the 2nd column
Range((Cells(varCurRow, 2)), (Cells(varCurRow, 2))).CopyFromRecordset rstDetail
'find where we are at in the recordset
varCurRow = varCurRow + varNumRow
'This is the last cell of our range calculation
Cells(varCurRow - 1, 14).Activate
varLastCell = ActiveCell.Address
'this is our calculation range
varAddRange = varFirstCell & ":" & varLastCell
Range(Cells((varCurRow), 14), Cells((varCurRow), 14)).Select
Range(varAddRange).Name = "range1"
ActiveCell.FormulaR1C1 = "=SUM(range1)"

 

[xlbo]

May I suggest an alternative - look at the subtotal function - you can specify where you want the subtotals to be put (ie after a change in value of a column) and which values to sum Rgds
~Geoff~

 

[sandylou]

Where can I find information on the SubTotal Function? I used to have VB installed on my machine and as a result had a wealth of examples. Now I can find some mediocre descriptions and not many examples.

 

[xlbo]

From VBA Help:
Subtotal Method
Creates subtotals for the range (or the current region, if the range is a single cell).
For information about using the Subtotal worksheet function in Visual Basic, see Using Worksheet Functions in Visual Basic.

Syntax

expression.Subtotal(GroupBy, Function, TotalList, Replace, PageBreaks, SummaryBelowData)

expression Required. An expression that returns a Range object.

GroupBy Required Long. The field to group by, as a 1-based integer offset. For more information, see the example.

Function Required Long. The subtotal function. Can be one of the following XlConsolidationFunction constants: xlAverage, xlCount, xlCountNums, xlMax, xlMin, xlProduct, xlStDev, xlStDevP, xlSum, xlVar, or xlVarP.

TotalList Required Variant. An array of 1-based field offsets, indicating the fields to which the subtotals are added. For more information, see the example.

Replace Optional Variant. True to replace existing subtotals. The default value is False.

PageBreaks Optional Variant. True to add page breaks after each group. The default value is False.

SummaryBelowData Optional Variant. Can be one of the following xlSummaryRow constants: xlSummaryAbove or xlSummaryBelow. The default value is xlSummaryBelow.


Example:
This example creates subtotals for the selection on Sheet1. The subtotals are sums grouped by each change in field one, with the subtotals added to fields two and three.

Worksheets("Sheet1&quot.Activate
Selection.Subtotal groupBy:=1, function:=xlSum, _
totalList:=Array(2, 3)

HTH Rgds
~Geoff~

 

[sandylou]

Thanks! I am trying it now. It sure takes a long time to run and the objects are not releasing at the end. Where is this VBA help? Online or do I need to install the developers edition of Office?

 

[xlbo]

Go to VB Editor and click on the Help button ;-) Rgds
~Geoff~

 

[sandylou]

Hey Thanks! I kind of have one more question and hope you don't think I am that ignorant, but I am not sure I understand the totalList property...Cause I used your example (which I still can't find in the help menu and I did simply click help in the vba editor), and i get a method of range subtotal failed... ....

 

[sandylou]

oohh...yeah i don't understand that totalList property at all...i get a prompt about labels and then it copies the label out about 40 columns and then has some wierd totals.. I want to just be able to after a specified selection n2:n17 get the sum for that range on n18 and then the sum for the next range n19:n50 on n51...this is all dynamic so like i always know my start cell and last cell...i can determine my range to sum, but when i was summing before, the sum would be the same throught the sheet...it just took the first range and put that as the amount on each subsequent ranges...

 

[sandylou]

xlbo??? any oter ideas???

 

[dougcranston]

XLBO..
Question if I may. Understand the concept which you detailed using VBA to dynamically create the subtotals.

However, is there a way to do it from VBScript. I am building an Excel Spreadsheet from queries against an MDB file.

Need to do subtotals, and need this to be automated. This is going to be a nightly report that needs to be emailed.

Have all the rest working. Correct data in the worksheets, but unable to adapt your code snippet to VBS. Combination ignorance and skillset.

My code looks like:

oXLWs.Range("A1:" & PickCol(fldmax) & rowmax)

oXLWs.Selection.Sort Key1:=oXLWs.Range("A2&quot, Order1:=1, Key2:=oXLWs.Range("B2&quot _
, Order2:=1, Key3:=oXLWs.Range("G2&quot, Order3:=1, Header:= _
0, OrderCustom:=1, MatchCase:=False, Orientation:=1

oXLWs.Selection.Subtotal GroupBy:=1, Function:=-4122, TotalList:=Array(8), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Code errors running under cscript indicating:
Microsoft VBScript compilation error: Expected Statement

Points to the first colon in the following piece of code.

V
oXLWs.Selection.Sort Key1:=oXLWs.Range("A2&quot, Order1:=1,

I have searched the web. This and other boards. MS and MSDN. Numerous books and finally resorted to looking at the various .HLP and .CHM files.

Get the same error running on NT vs Win2000.

Any suggestions or places I might look at would be greatly appreciated.

Thanks

Doug Cranston

 

[xlbo]

So it is the SORT not the SUBTOTAL that is giving the error ??

Can't quite see from the code what is happening but sorts can be a little tempremental if the sheet is not selected 1st:
Try using with styatements as they make it easier to see which part of the object model you are dealing with eg

With oXLWs
.Range("A1:" & PickCol(fldmax) & rowmax).select
with .selection
.Sort Key1:=.Range("A2&quot, Order1:=1, Key2:=.Range("B2&quot , Order2:=1, Key3:=.Range("G2&quot, Order3:=1, Header:= 0, OrderCustom:=1, MatchCase:=False, Orientation:=1

.Subtotal GroupBy:=1, Function:=-4122, TotalList:=Array(8), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
end with
end with Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[dougcranston]

Geoff/xlbo

You were right in your identification of where the problem was appearing. SORT. However, if I comment out the sort and have the SUBTOTAL errors with the same error message.

Tried your code and I still get the same error.

Tried that and get the same resulting errors.

One thing just dawned on me is my code has two recordsets. First has a criteria used to limit/filter records in the second recordset AND used to name individual worksheets in the workbook. (Application involves identifying the qty of work requests by store within a region. Each region is a separate worksheet.) Could this have anything to do with that?

Would you be willing to look at my code? It is fairly lengthy for posting. Over 760 lines.. Lots of comments and extras(emailing). Access db is 478K. Could cut out alot to shrink it and still provide a workable size for testing.

FYI-If I comment out your code or my version, it runs, just no subtotals.

Is there any other suggestions? Don't want to impose but would welcome them.

Thanks in advance.

Doug Cranston

 

[xlbo]

I have a lot on today so won't be able to look but if this is not a massive priority, please feel free to send me the file and I will look at it next week. If this is ok, post your email address and I will send you a note which you can then reply to

The only thing that strikes me at the mo is that you may only have 7 columns of data but the subtotals function is summing on an 8th column.....
Also - have you tried stepping thru the code (use F8) and seeing what oXLWs evaluates to and what your PickCol and rowMax evaluate to ?? Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[dougcranston]

Geoff/xlbo..

I did not respond before this because I felt I needed to push it further without imposing on you and your offer.

I have spent all my available time and I alot of research and I have got the script to work... Sort of. Found that the VBS approach did not like the: Key1:= type coding. Found a reference on a Web page that tipped me off.

I can successfully create a Workbook, pull the data for the first worksheet, Name it appropropriately, populate the data, format (borders and etc.) AND do the Subtotaling for the first recordset (Region Identifier for want of a description.)

HOWEVER, when it loops through to do the next Region, the worksheet/query it dies.

I am convinced it is because it is not sure which sheet it is supposed to be working on ... Something that has to do with the Subtotaling. Reason I say that is that if I use a copy of the same script sans the Sort/Subtotal routines it runs like a champ. Admittedly sans the Sort/Subtotals.

If your offer still stands, would welcome you taking a look at it. my email address is douglas.cranston@verizon.com

I suspect it is something totally obvious and stupid. I am good at shooting myself in my foot. Have no one here at work that can help.

If you are tied up, fully understand and just thank you for your guidance. I am alot further along. Just can't seem to get over the hump.

Take care.

Doug Cranston