Posted to microsoft.public.excel.programming
|
|
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
|