Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can i use IsEmpty and IF this way? What's wrong here? | Excel Programming | |||
Can i use IsEmpty and IF this way? What's wrong here? | Excel Programming | |||
Can i use IsEmpty and IF this way? What's wrong here? | Excel Programming | |||
Help with IsEmpty | Excel Programming | |||
isempty | Excel Programming |