|
Steve Bullins
Using XL 2003 and 2000
The following Function is from Steve Bullen via j-walk.com. It was written for XL 97 up Obviously, there is something probably fairly obvious that I am missing. If fact when I get that answer you can say DA! I placed this VBA code in "ThisWorkbook" Then I placed this formula into cell A1: =FilterCriteria(A3) Cell A3 contains the Autofilter down-arrow for column A No matter what I do I get a #NAME error. I checked spelling; protection; duplicate function names, etc. It seems that XL does not "see" this Function. Is it a protected name? ************************************************** *********** 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 ************************************************** *********** TIA Dennis |
"Dennis" wrote: Using XL 2003 and 2000 The following Function is from Steve Bullen via j-walk.com. It was written for XL 97 up Obviously, there is something probably fairly obvious that I am missing. If fact when I get that answer you can say DA! I placed this VBA code in "ThisWorkbook" Then I placed this formula into cell A1: =FilterCriteria(A3) Cell A3 contains the Autofilter down-arrow for column A No matter what I do I get a #NAME error. I checked spelling; protection; duplicate function names, etc. It seems that XL does not "see" this Function. Is it a protected name? ************************************************** *********** 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 ************************************************** *********** TIA Dennis |
Hi Dennis
what happens if you put it in a normal module (insert / module) rather than ThisWorkbook? Cheers JulieD "Dennis" wrote in message ... Using XL 2003 and 2000 The following Function is from Steve Bullen via j-walk.com. It was written for XL 97 up Obviously, there is something probably fairly obvious that I am missing. If fact when I get that answer you can say DA! I placed this VBA code in "ThisWorkbook" Then I placed this formula into cell A1: =FilterCriteria(A3) Cell A3 contains the Autofilter down-arrow for column A No matter what I do I get a #NAME error. I checked spelling; protection; duplicate function names, etc. It seems that XL does not "see" this Function. Is it a protected name? ************************************************** *********** 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 ************************************************** *********** TIA Dennis |
Don't put the code in the ThisWorkbook module.
Put it in a regular General module. Dennis wrote: Using XL 2003 and 2000 The following Function is from Steve Bullen via j-walk.com. It was written for XL 97 up Obviously, there is something probably fairly obvious that I am missing. If fact when I get that answer you can say DA! I placed this VBA code in "ThisWorkbook" Then I placed this formula into cell A1: =FilterCriteria(A3) Cell A3 contains the Autofilter down-arrow for column A No matter what I do I get a #NAME error. I checked spelling; protection; duplicate function names, etc. It seems that XL does not "see" this Function. Is it a protected name? ************************************************** *********** 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 ************************************************** *********** TIA Dennis -- Dave Peterson |
Thanks Julie & Dave
I believe that the instructions said "ThisWorkbook" but I should have know better. This issue has come up before in my XL VBA history - but I forgot about it! You now can pin the DA! award on my tail. Dennis "Dave Peterson" wrote: Don't put the code in the ThisWorkbook module. Put it in a regular General module. Dennis wrote: Using XL 2003 and 2000 The following Function is from Steve Bullen via j-walk.com. It was written for XL 97 up Obviously, there is something probably fairly obvious that I am missing. If fact when I get that answer you can say DA! I placed this VBA code in "ThisWorkbook" Then I placed this formula into cell A1: =FilterCriteria(A3) Cell A3 contains the Autofilter down-arrow for column A No matter what I do I get a #NAME error. I checked spelling; protection; duplicate function names, etc. It seems that XL does not "see" this Function. Is it a protected name? ************************************************** *********** 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 ************************************************** *********** TIA Dennis -- Dave Peterson |
| All times are GMT +1. The time now is 10:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com