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

Thanks Tom,
The problem that i am facing now is the filtering results doesnt matc
my expectation.
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).

I'm not sure if this is correct, but it seems like it have nothing t
do with reset the autofilter .
The code:

Code
-------------------
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"
-------------------

have succesfully return the value of F38039 that match field 5'
filtering at each Cells(38041 + j, 6 + i).

however the following code:


Code
-------------------
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"
-------------------

failed to return the value of F38039 that match field 6's filtering.

attached is the problem i looks like.
the sgm8 is expected to give some value..but it doesnt.

i dont know what's goes wrong here. Can you teach me? Thanks
attached is the problem i looks like

+-------------------------------------------------------------------
|Filename: sgm8.jpg
|Download: http://www.excelforum.com/attachment.php?postid=2745
+-------------------------------------------------------------------

--
changeabl
-----------------------------------------------------------------------
changeable's Profile: http://www.excelforum.com/member.php...fo&userid=1571
View this thread: http://www.excelforum.com/showthread.php?threadid=27452

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

Turn on the macro recorder and filter the worksheets manually to get a
successful return.

that will show you what criteria you would need to use. It seems strange to
me filter with an AND argument for equality to two specific values, but then
maybe you have an inequality operator in your array values - I certainly
don't know.

I am not in the excel forum so I can't access your attachment.

--
Regards,
Tom Ogilvy

"changeable" wrote in message
...

Thanks Tom,
The problem that i am facing now is the filtering results doesnt match
my expectation.
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).

I'm not sure if this is correct, but it seems like it have nothing to
do with reset the autofilter .
The code:

Code:
--------------------
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")
--------------------

have succesfully return the value of F38039 that match field 5's
filtering at each Cells(38041 + j, 6 + i).

however the following code:


Code:
--------------------
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")
--------------------

failed to return the value of F38039 that match field 6's filtering.

attached is the problem i looks like.
the sgm8 is expected to give some value..but it doesnt.

i dont know what's goes wrong here. Can you teach me? Thanks
attached is the problem i looks like.


+-------------------------------------------------------------------+
|Filename: sgm8.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=2745 |
+-------------------------------------------------------------------+

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