Filter

[Wayne]

I`m back with one more question.I`m using the Function below I know its close
to what I want.The only thing is when I use it once it will not change the
value a second time is there a part of a code that I am missing.

A1 has formula =FilterCriteria(A3) with Auto filter in A3


Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
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 xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function

 

[Elkar]

The code looks fine. Try changing the cell reference in your formula to
match the first row of your data rather than the header. In this case:

=FilterCriteria(A4)

HTH,
Elkar

 

[Wayne]

That was too easy Thanks so much.

The code looks fine. Try changing the cell reference in your formula to
match the first row of your data rather than the header. In this case:

=FilterCriteria(A4)

HTH,
Elkar

 

[Wayne]

Theres 1 more problem the result for A1 when I use the filter comes up with =
how do I get rid of that because I use a Vlookup formula for that cell and it
comes up NA.

 

[Elkar]

Hmm.... perhaps something like this. Change the last 3 lines following the
second "End With" to:

FilterCriteria = Right(Filter,Len(Filter)-1)
Finish:
End Function

HTH,
Elkar

 

[Dave Peterson]

You could get rid of the = sign in the UDF, but you may not want to. There are
other criteria that would make that first character important (like greater than
or equal to).

You may want to just ignore the first character in your =vlookup() statement
when you know that first character is an equal sign:

=vlookup(mid(filtercriteria(a4),2,255, .....

(Use a number larger than the string that can be returned--I used 255 in my
example.)

 

[Wayne]

Thanks it works the = is gone.For some reason its not doing the Vlookup even
when the right value appears.Is there a private sub or something that I need
to have to get this to work.

 

[Dave Peterson]

ps.

If your criteria is a number and you want that treated as a number (not a
string):

=vlookup(--mid(filtercriteria(a4),2,255, .....

the -- stuff changes text numbers to number numbers.

 

[Dave Peterson]

I missed a closing paren on both formulas...

=vlookup(mid(filtercriteria(a4),2,255), .....
=vlookup(--mid(filtercriteria(a4),2,255), .....

 

[Wayne]

Thanks for your reply.I have only been working with VBA for a little while I
do not know what UDF means was able to get ride of the = sign but I cant get
the Vlooku() to work when the right value appears.Could you please show me an
example on how to do this.Thanks in advance

 

[Dave Peterson]

UDF means User Defined Function. That's what =filtercriteria() is. It's a
function that is written by a user (you!).

Why doesn't the =vlookup() work?

Maybe there isn't a match?

What does =filtercriteria(a4) return?

Are you sure that it's in the first column of the range?

Debra Dalgleish has some trouble shooting info:
http://www.contextures.com/xlFunctions02.html#Trouble

 

[Wayne]

My sample is set up like this
A1 =FilterCriteria(A4) B1 =IF(A1="","",VLOOKUP(A1,A4:A8,2))
A3 Has Auto Filter Arrow
A4 10
A5 20
A6 30
A7 40
A8 50 B8 Wayne

When I select 50 I want it to bring up Wayne in B8

=filtercriteria returns the value that I pick from my list.Every other
Vlookup formula that I use works I cant figure out this one.

 

[Wayne]

Dave I tried formating the cell to Numbers it works great Thanks for all your
help.