C
changeable
Thanks Tom,
I tried to include the
Code:
--------------------
if Activesheet.filterMode then
ActiveSheet.ShowAll
End If
--------------------
but an error occur.
Therefore, i come out with another "reset" filter method by adding
Code:
--------------------
Selection.AutoFilter Field:=5 'reset
Selection.AutoFilter Field:=6 'reset
--------------------
the code now look like this:
Code:
--------------------
For j = 1 To 1
For i = 1 To 10
If Not (IsEmpty(arr.Cells(i, 2)) And IsEmpty(arr.Cells(i, 3))) Then
Selection.AutoFilter Field:=5, Criteria1:=arr(i, 2), Operator:=xlAnd, Criteria2:=arr(i, 3)
Cells(38041 + j, 6 + i).Value = Range("F38039")
ElseIf (IsEmpty(arr.Cells(i, 2)) And IsEmpty(arr.Cells(i, 3))) Then
Selection.AutoFilter Field:=6, Criteria1:=arr(i, 4), Operator:=xlAnd, Criteria2:=arr(i, 5)
Cells(38041 + j, 6 + i).Value = Range("F38039")
End If
Selection.AutoFilter Field:=5 'reset
Selection.AutoFilter Field:=6 'reset
Next i
Next j
--------------------
(My 'j' is prepared for other loop which cant insert to my code at
current status.)
The code works well for satisfying the (arr.Cells(i, 2) AND
(arr.Cells(i, 3)criteria (which involve field 5 in filtering) but it
fails to work while i want to satisfy the (arr.Cells(i, 4) AND
(arr.Cells(i, 5) criteria (which involve field 6 in filtering).
Why does this happen? How can i solve it?
I tried to include the
Code:
--------------------
if Activesheet.filterMode then
ActiveSheet.ShowAll
End If
--------------------
but an error occur.
Therefore, i come out with another "reset" filter method by adding
Code:
--------------------
Selection.AutoFilter Field:=5 'reset
Selection.AutoFilter Field:=6 'reset
--------------------
the code now look like this:
Code:
--------------------
For j = 1 To 1
For i = 1 To 10
If Not (IsEmpty(arr.Cells(i, 2)) And IsEmpty(arr.Cells(i, 3))) Then
Selection.AutoFilter Field:=5, Criteria1:=arr(i, 2), Operator:=xlAnd, Criteria2:=arr(i, 3)
Cells(38041 + j, 6 + i).Value = Range("F38039")
ElseIf (IsEmpty(arr.Cells(i, 2)) And IsEmpty(arr.Cells(i, 3))) Then
Selection.AutoFilter Field:=6, Criteria1:=arr(i, 4), Operator:=xlAnd, Criteria2:=arr(i, 5)
Cells(38041 + j, 6 + i).Value = Range("F38039")
End If
Selection.AutoFilter Field:=5 'reset
Selection.AutoFilter Field:=6 'reset
Next i
Next j
--------------------
(My 'j' is prepared for other loop which cant insert to my code at
current status.)
The code works well for satisfying the (arr.Cells(i, 2) AND
(arr.Cells(i, 3)criteria (which involve field 5 in filtering) but it
fails to work while i want to satisfy the (arr.Cells(i, 4) AND
(arr.Cells(i, 5) criteria (which involve field 6 in filtering).
Why does this happen? How can i solve it?