PDA

View Full Version : [SOLVED] Autofilter OR function



U_Shrestha
05-07-2008, 06:58 AM
Hi all,

I need help with following code. What I want to do is, when a command button is pressed, I want to filter the table if column B or column C has the value *S/A* or *I/C*. The lines I came up with don't work. I have also attached the sample WB. Thanks.


Private Sub CommandButton1_Click()
With Selection
.AutoFilter field:=2, Criteria1:="*I/C*", _
Operator:=xlOr, _
Criteria2:="*S/A*"
.AutoFilter field:=3, Criteria1:="*S/A*", Operator:=xlOr, _
Criteria2:="*I/C*"
End With
End Sub

Bob Phillips
05-07-2008, 08:25 AM
The rows that only have that value in one of the columns are being filtered out by the other columns filter.

I think you need a helper column with a formula, and filter on that.

U_Shrestha
05-07-2008, 08:32 AM
thanks xld, will work one that.

Bob Phillips
05-07-2008, 08:37 AM
Here is a suggested formula to get you started


=SUM(COUNTIF(B2:C2,{"I/C","S/A"}))>0

it is an array formula.

Bob Phillips
05-07-2008, 08:38 AM
Actually, I lie. It is not an array formula, it is a common old non-array formula.

U_Shrestha
05-07-2008, 08:55 AM
I dragged the formula in column D and the results came as TRUE or FALSE (which is perfect). My only confusion is the curly brackets. Will I be ok with dragging down the formula and I won't ever need to enter CSE each time? Thanks.

Bob Phillips
05-07-2008, 09:19 AM
Never any need to enter CSE, it is not an array formula.

U_Shrestha
05-07-2008, 09:29 AM
Thank you very much xld for your help again . That solves my problem. I have never seen a curly bracket inside a regular formula though, I wonder what the logic is :thinking:

Bob Phillips
05-07-2008, 09:44 AM
That is an array constant, it just means all values in that arry get tested against.

I could have used


=OR(B2="I/C",B2="S/A",C2="I/C",C2="S/A")

but the other seems neater, especially when I originally thought we were looking at contains, which would have been


=OR(ISNUMBER(FIND("I/C",B2)),ISNUMBER(FIND("S/A",B2)),ISNUMBER(FIND("I/C",C2)),ISNUMBER(FIND("S/A",C2)))

as against


=SUM(COUNTIF(B2:C2,{"*I/C*","*S/A*"}))>0

U_Shrestha
05-07-2008, 09:52 AM
that does seem very neat! thanks you very much for the explanation.

Bob Phillips
05-07-2008, 09:54 AM
Neater, and simpler to determine its purpose just by reading it IMO.