Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with the code,pls hv a look
First of wll, i would like to apology for my bad english. I am trying to do a filtering based on multiple criteria that the valu of criteria needs to be change to next value after each filter proces complete. my criteria is placed in a worksheet named wksCriteria the data that need to be filter is placed in worksheet named wksAug both worksheets placed in workbook Aug_f1.xls The field i need to filter is place in column E and F, after eac filtering i need the subtotal(average and standard deviation) of Colum G be recorded. I have attached my worksheet and some data sample. The workshee included the following code which i am trying to do. But i have faile to do so. Please comment and tell me what to do. Thanks!!! Code ------------------- Sub criteria() Dim arr As Variant Dim wksCriteria As Worksheet Dim wksAug As Worksheet Dim intRowCount As Integer Dim i As Integer 'put the criteria into an array Set arr = wksCriteria.Range("B2:E128") intRowCount = wksCriteria.Range("B2").CurrentRegion.Rows.Count 'cycle through the array and filter the data For i = 0 To intRowCount wksAug.Range("E1").Select Selection.AutoFilter field:=5, Criteria1:=arr(i, 1), Operator:=xlAnd, Criteria2:=arr(i, 2) MsgBox wksCriteria.Range("G2") Next i End Su ------------------- -- changeabl ----------------------------------------------------------------------- changeable's Profile: http://www.excelforum.com/member.php...fo&userid=1571 View this thread: http://www.excelforum.com/showthread.php?threadid=27444 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with the code,pls hv a look
I'm not sure what the field is in column E and F means. Is it one field or two?
But you may want to look at Data|pivottable. It sounds like it's what you want. You can get all the subtotals for each value in that field. changeable wrote: First of wll, i would like to apology for my bad english. I am trying to do a filtering based on multiple criteria that the value of criteria needs to be change to next value after each filter process complete. my criteria is placed in a worksheet named wksCriteria the data that need to be filter is placed in worksheet named wksAug both worksheets placed in workbook Aug_f1.xls The field i need to filter is place in column E and F, after each filtering i need the subtotal(average and standard deviation) of Column G be recorded. I have attached my worksheet and some data sample. The worksheet included the following code which i am trying to do. But i have failed to do so. Please comment and tell me what to do. Thanks!!! Code: -------------------- Sub criteria() Dim arr As Variant Dim wksCriteria As Worksheet Dim wksAug As Worksheet Dim intRowCount As Integer Dim i As Integer 'put the criteria into an array Set arr = wksCriteria.Range("B2:E128") intRowCount = wksCriteria.Range("B2").CurrentRegion.Rows.Count 'cycle through the array and filter the data For i = 0 To intRowCount wksAug.Range("E1").Select Selection.AutoFilter field:=5, Criteria1:=arr(i, 1), Operator:=xlAnd, Criteria2:=arr(i, 2) MsgBox wksCriteria.Range("G2") Next i End Sub -------------------- -- changeable ------------------------------------------------------------------------ changeable's Profile: http://www.excelforum.com/member.php...o&userid=15714 View this thread: http://www.excelforum.com/showthread...hreadid=274446 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is wrong with this code? | Excel Discussion (Misc queries) | |||
What is wrong with the code? | Excel Discussion (Misc queries) | |||
Can someone tell me what's wrong with this code please? | Excel Programming | |||
What is wrong with this code? | Excel Programming | |||
what's wrong with my code???? | Excel Programming |