Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
That was too easy Thanks so much.
"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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
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 "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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
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. "Elkar" wrote: 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 "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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
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 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
I missed a closing paren on both formulas...
=vlookup(mid(filtercriteria(a4),2,255), ..... =vlookup(--mid(filtercriteria(a4),2,255), ..... Dave Peterson wrote: 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 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter
Dave I tried formating the cell to Numbers it works great Thanks for all your
help. "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |