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" <[email protected]> wrote in message
news:[email protected]...
> 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
>