Can i use IsEmpty and IF this way? What's wrong here?
 

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?


--
changeable
------------------------------------------------------------------------
changeable's Profile: http://www.excelforum.com/member.php...o&userid=15714
View this thread: http://www.excelforum.com/showthread...hreadid=274524

Can i use IsEmpty and IF this way? What's wrong here?
 

I had a typo: (someone was pressing me to do something else, so I sent it
too hastily).

It should be:

Sub AABBCC()
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

End Sub


Then you don't have to worry about which columns are filtered.

--
Regards,
Tom Ogilvy

"changeable" wrote in message
...

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?


--
changeable
------------------------------------------------------------------------
changeable's Profile:
http://www.excelforum.com/member.php...o&userid=15714
View this thread: http://www.excelforum.com/showthread...hreadid=274524