PDA

View Full Version : Subtotal for multiple groups based on value in a filtered list



kenyayvette
09-05-2008, 04:24 PM
Hi. I am trying to do subtotals based on a filtered list. I have to loop through the unique values of 2 separate columns and do an average of the filtered results. I'm using Range().specialcells(xlcelltypebisible).formula = distributedSales, but what is happening is that everytime this line is called in the loop, it updates not only the currently visible cell, but every other cell that was visible before. So by the end of the inner loop, every value in range B gets updated to the same value. Is there something I need to do to clear the visible cells from the last operation I did?

Here is the specific code I've written. I need to loop on the contract first, then the CSC. At the end of the loop, if I filter only on Contract and show all of the CSC's, they all show the same value.

Please let me know if you need an actual sample of data.


'for each contract filter by contract
For Each contract In Range(ContractRange).Cells
contractnumber = contract.Value
Sheets("Data").Select
Range("A1").Select
' Selection.AutoFilter 1, contractnumber
'For each filtered contract, filter by CSC
For Each CSC In Range(cscrange).Cells
Range("A1").Select
Selection.AutoFilter 1, contractnumber

ActiveSheet.Range("A1:" & lastcelladdress).AutoFilter 9, CSC

'subtotal the total sales column
Range("Q1").Select
subrange = Selection.Address(0, 0) & ":" _
& Selection.End(xlDown).Address(0, 0)
cscsubtotformula = "=Subtotal(109," & subrange & ")"
Range("w1").Formula = cscsubtotformula
'subtotal the number of facils
cscsubcountformula = "=subtotal(103," & subrange & ") - 1"
Range("X1").Formula = cscsubcountformula
'Replace total sales for all facil with distrib CSC sales numbers
'need to actually find the first cell
'ActiveCell.Offset(1, 0).Select
Range("W1:X1").Select
Selection.Copy
Selection.PasteSpecial xlPasteValues
distribCscSales = "=$W$1/$X$1"
Range("Q1").Select
Range("Q1", Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Formula = distribCscSales
Range("Q1").Value = "Total Sales"
ActiveSheet.AutoFilterMode = False
Next CSC
ActiveSheet.AutoFilterMode = False
Call PastePageValues
Next contract

mdmackillop
09-06-2008, 02:11 AM
Welcome to VBAX
Can you post some sample data? Use Manage Attachments in the Go Advanced reply section.
Regards
MD

kenyayvette
09-08-2008, 07:51 AM
Hi. I've uploaded a sample spreadsheet - I've put in placeholder columns where I have data that I've removed for confidentiality. In my data, I also have rows that do not have CSCs, but I have filtered them out to keep the size of the spreadsheet down.

I am using Office 2007 if that makes a difference.

Bob Phillips
09-08-2008, 08:52 AM
Are you saying that you want the total sales for Walmart, CSC 921591 as an example? If so, where are these values to go?

kenyayvette
09-09-2008, 08:54 AM
Good morning.
What I would like to see is for contract 7392 and CSC 921591, the average of the total sales column. So for those 8 visible fields, the total sales column should be updated to 5571.31. For contract 12628, CSC 144771, for each of the 16 visible rows, the total sales column should be updated to show 73364.65. This contract/csc combination does include a row attributable to Target. Please note that the totals are not dependent on the company at all, my data just shows a proliferation of Walmarts :)

And I did just learn that I could have used subtotal to get the average of the visible cells instead of using subtotal(sum)/subtotal(counta).....