filter & show filter

[dantb]

Hi all, what I am trying to do is use a vba filter, and have it show what I filtered on in the above cell (a1). I found this code but can't seem to make it work. I put it in a modual, and on the filter sheet (sheet1),cell a1,I put =FilterCriteria A3
but it dont seem to work. Thanks for any help Dan

Here is the Modual Code:

Function FilterCriteria(Rng As Range) As String
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case x1And
Filter = Filter & " AND " & .Criteria2
Case x1Or
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish
FilterCriteria = Filter
End Function

 

[Colo]

Hi dantb,

Please try to put as follows.
=FilterCriteria(A3)

And you need modify the code a little. Please note bold text.<PRE><FONT color=red>Function</FONT>FilterCriteria(Rng<FONT color=red>As</FONT> Range)<FONT color=red>As</FONT><FONT color=red> String</FONT><FONT color=red>Dim</FONT>Filter<FONT color=red>As</FONT><FONT color=red> String</FONT>

Filter = ""<FONT color=red>On Error</FONT><FONT color=red>GoTo</FONT> Finish<FONT color=red>With</FONT>Rng.Parent.AutoFilter<FONT color=red>If</FONT>Intersect(Rng, .Range) Is<FONT color=red> Nothing</FONT><FONT color=red> Then</FONT><FONT color=red>GoTo</FONT> Finish<FONT color=red>With</FONT>.Filters(Rng.Column - .Range.Column + 1)<FONT color=red>If</FONT>Not .On<FONT color=red> Then</FONT><FONT color=red>GoTo</FONT> Finish

Filter = .Criteria1

Select<FONT color=red>Case</FONT>.Operator<FONT color=red>Case</FONT>xlAnd<FONT color=#339966>'//Not X1</FONT>
Filter = Filter & " AND " & .Criteria2<FONT color=red>Case</FONT>xlOr<FONT color=#339966>'//Same sa above</FONT>
Filter = Filter & " OR " & .Criteria2<FONT color=red>End Select</FONT><FONT color=red>End With</FONT><FONT color=red>End With</FONT>

Finish:

FilterCriteria = Filter<FONT color=red>End Function</FONT></PRE>


_________________
HTH

With Regards,
Colo

This message was edited by Colo on 2002-10-30 20:59
This message was edited by Colo on 2002-10-30 21:00
This message was edited by Colo on 2002-10-30 21:01

 

[dantb]

Thank you so much Colo, works great, just what I was looking for. Thanks again & have a good one Dan !.