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

Hi all!
i have a code looks like this:



code:
--------------------------------------------------------------------------------
Sub criteria()
Dim arr As Variant
Dim i As Integer
Dim j As Integer
'put the criteria into an array
Set arr = Range("A38042:E38168")
Range("E1").Select
For i = 1 To 10
If IsEmpty(arr.Cells(i, 2) And arr.Cells(i, 3)) = False Then
Selection.AutoFilter Field:=5, Criteria1:=arr(i, 2)
Operator:=xlAnd, Criteria2:=arr(i, 3)
Cells(38041 + j, 6 + i).Value = Range("F38039")
End If
Next i
End Sub
--------------------------------------------------------------------------------




what i am trying to do is :
if the 2nd col and 3rd col(of arr) is not empty then
Selection.AutoFilter Field:=5, Criteria1:=arr(i, 2), Operator:=xlAnd
Criteria2:=arr(i, 3)
Else if
if the 4th col and 5th col(of arr) is not empty then
Selection.AutoFilter Field:=6, Criteria1:=arr(i, 4), Operator:=xlAnd
Criteria2:=arr(i, 5)
ElseIf
if the 3th col and 4th col(of arr) is not empty then
Selection.AutoFilter Field:=5, Criteria1:=arr(i, 3)
Selection.AutoFilter Field:=6, Criteria1:=arr(i, 4)
ElseIf
if the 1st col and 5th col(of arr) is not empty then
Selection.AutoFilter Field:=5, Criteria1:=arr(i, 1)
Selection.AutoFilter Field:=6, Criteria1:=arr(i, 5)



what's wrong with my IF there

--
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?
 

If IsEmpty(arr.Cells(i, 2) And arr.Cells(i, 3)) = False Then

would be

If Not (IsEmpty(arr.Cells(i, 2)) And IsEmpty(arr.Cells(i, 3))) Then

--
Regards,
Tom Ogilvy

"changeable" wrote in message
...

Hi all!
i have a code looks like this:



code:
--------------------------------------------------------------------------
------
Sub criteria()
Dim arr As Variant
Dim i As Integer
Dim j As Integer
'put the criteria into an array
Set arr = Range("A38042:E38168")
Range("E1").Select
For i = 1 To 10
If IsEmpty(arr.Cells(i, 2) And arr.Cells(i, 3)) = False Then
Selection.AutoFilter Field:=5, Criteria1:=arr(i, 2),
Operator:=xlAnd, Criteria2:=arr(i, 3)
Cells(38041 + j, 6 + i).Value = Range("F38039")
End If
Next i
End Sub
--------------------------------------------------------------------------
------




what i am trying to do is :
if the 2nd col and 3rd col(of arr) is not empty then
Selection.AutoFilter Field:=5, Criteria1:=arr(i, 2), Operator:=xlAnd,
Criteria2:=arr(i, 3)
Else if
if the 4th col and 5th col(of arr) is not empty then
Selection.AutoFilter Field:=6, Criteria1:=arr(i, 4), Operator:=xlAnd,
Criteria2:=arr(i, 5)
ElseIf
if the 3th col and 4th col(of arr) is not empty then
Selection.AutoFilter Field:=5, Criteria1:=arr(i, 3)
Selection.AutoFilter Field:=6, Criteria1:=arr(i, 4)
ElseIf
if the 1st col and 5th col(of arr) is not empty then
Selection.AutoFilter Field:=5, Criteria1:=arr(i, 1)
Selection.AutoFilter Field:=6, Criteria1:=arr(i, 5)



what's wrong with my IF there?


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