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