PDA

View Full Version : [SOLVED] Advanced Filter Criteria Non Adjacent Cells



pegbol
06-14-2005, 06:28 PM
.
.
Hello Masters,
One more time, I request your help. The problem is the next:

I need an event code (Worksheet_Change) to filter (advanced filter) my table based on 3 criteria that are in non adjacent cells.

I enclose an example file :help

Thanks so much for your kind assistance.

regards,
Pedro
.
.

acw
06-14-2005, 11:28 PM
Hi

Try this. The dates in the cells B3 and B6 must be in the same format as the data being filtered. So B3 has to be >=15-Mar-2005 and B6 has to be <=30-Apr-2005



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Union(Range("b3"), Range("b6"), Range("e5"))) Is Nothing Then
Sheets("hoja1").Range("b9").AutoFilter
Sheets("hoja1").Range("b8").AutoFilter Field:=1, Criteria1:=Sheets("hoja1").Range("b3"), Operator:= _
xlAnd, Criteria2:=Sheets("hoja1").Range("b6")
Selection.AutoFilter Field:=3, Criteria1:=Sheets("hoja1").Range("e5")
End If
End Sub


Tony

pegbol
06-15-2005, 06:18 AM
.
.

Thank you Tony,

I implemented your code , but doesn't seem to work.


On the other hand, I adapted to my workbook an example of Mrs. Debra Dalgleish.
It works: Now, only have the problem with the range of dates:
>=15-Mar-05
<=30-Apr-05

How can this be done ?.

Help Masters.

Enclosed is my file with advanced filter (VBA code).

regards,
Pedro
.
.

Bob Phillips
06-15-2005, 07:21 AM
It works: Now, only have the problem with the range of dates:
>=15-Mar-05
<=30-Apr-05

Hola Pedro,

?Es alambique dif?cil all??

Anyway, to your problem.


Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B3,B6,E5")) Is Nothing Then
Range("B8").AutoFilter
Range("base").AutoFilter Field:=1, _
Criteria1:=">=" & Format(Range("B3").Value, "dd-mmm-yyyy"), _
Operator:=xlAnd, _
Criteria2:="<=" & Format(Range("B6").Value, "dd-mmm-yyyy")
Range("base").AutoFilter Field:=3, _
Criteria1:="=" & Range("E5").Text
End If
End With
End Sub

pegbol
06-15-2005, 08:21 AM
.
.
Hola xld!!!!!!:hi:

Muchas gracias por tu amable respuesta.
Yep, things were pretty heavy here. But, now it's better.

Well, definitely I am doing something wrong, cause I get an error with your code "error 1004. Range Worksheet".



Only a favor xld.
I would rather to use and Advanced Filter instead of an Autofilter (I apologize for being so obstinate).

Is there any way to modify and/or improve the next code ?. (advfilter2.xls)


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$5" Or _
Target.Address = "$B$3" Or _
Target.Address = "$B$6" Then
Range("base").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("criterio"), Unique:=False
End If
End Sub

the criteria "criterio" in cell G2 has the next formula:

=AND(OR($E$5="",D9=$E$5),ISNUMBER(SEARCH($B$3,B9)),ISNUMBER(SEARCH($B$6,B9)))

As I said: The above code works. Only have the problem with the range of dates:
>=15-Mar-05
<=30-Apr-05



Thanks for your kind attention, Master Jedi. :bow:

saludos,
Pedro
.
.

Bob Phillips
06-15-2005, 10:09 AM
Yep, things were pretty heavy here. But, now it's better.

Glad it has quietened down. I thought of you when I heard the news.


I would rather to use and Advanced Filter instead of an Autofilter (I apologize for being so obstinate).

We can do, I would just add that normally Autofilter is fine for 2 criteria. You have 3, but only 2 on 1 filed and 1 on another. Problem with advanced filter is we have to create a criteria separate as your data will force an OR condition.


Is there any way to modify and/or improve the next code ?. (advfilter2.xls)

There is nothing wrong with the code, it is in the criteria definition, See my attachment.


saludos,
Bob

pegbol
06-15-2005, 12:26 PM
.
.

Bob, muchas gracias por el "attachment".


Yes, now the macro works excellent and filter the desired data.:thumb :clap: :clap: :clap:


Como siempre, muchas gracias por tu amable ayuda.

un gran saludo desde La Paz,
Pedro

:beerchug:
.
.