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.

"Dave Peterson" wrote:

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 wrote:

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" wrote:

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 wrote:

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" wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson