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

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