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
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