VBA function returns #NAME?
 

I have tried to add the below VBA-code from
http://www.ozgrid.com/VBA/autofilter-criteria.htm in a new module, but
it continues to return #NAME? when I try to use the function:

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String

Application.Volatile

With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)

If Not .On Then Exit Function

strCri1 = .Criteria1
If .Operator = xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr Then
strCri2 = " OR " & .Criteria2
End If

End With
End With

AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2
End Function

Does this indicate an error in the VBA syntax or perhaps that the module
is not made available?

Mogens

VBA function returns #NAME?
 

Where are you putting the code? It should be in a regular code
module, NOT the ThisWorkbook code module or a sheet module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"mogens" wrote in message
...
I have tried to add the below VBA-code from
http://www.ozgrid.com/VBA/autofilter-criteria.htm in a new
module, but it continues to return #NAME? when I try to use the
function:

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String

Application.Volatile

With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)

If Not .On Then Exit Function

strCri1 = .Criteria1
If .Operator = xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr Then
strCri2 = " OR " & .Criteria2
End If

End With
End With

AutoFilter_Criteria = UCase(Header) & ": " & strCri1 &
strCri2
End Function

Does this indicate an error in the VBA syntax or perhaps that
the module is not made available?

Mogens

VBA function returns #NAME?
 

How do you call the function (what is your formula)?
Did you try inserting it via the Function wizard?

--
Kind regards,

Niek Otten

"Chip Pearson" wrote in message ...
Where are you putting the code? It should be in a regular code module, NOT the ThisWorkbook code module or a sheet module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"mogens" wrote in message ...
I have tried to add the below VBA-code from http://www.ozgrid.com/VBA/autofilter-criteria.htm in a new module, but it continues
to return #NAME? when I try to use the function:

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String

Application.Volatile

With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)

If Not .On Then Exit Function

strCri1 = .Criteria1
If .Operator = xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr Then
strCri2 = " OR " & .Criteria2
End If

End With
End With

AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2
End Function

Does this indicate an error in the VBA syntax or perhaps that the module is not made available?

Mogens

VBA function returns #NAME?
 

Chip Pearson wrote:
Where are you putting the code? It should be in a regular code
module, NOT the ThisWorkbook code module or a sheet module.


Thanks Chip - that explained the error.

I am trying to find a way to see how to qualify for instance how to
select only records with "blanks". This formula didn't give much of an
explanation though.

A shame that you are not able to switch between different criteria for
an autofilter as the advanced filter is a mystery to me (doesn's seem to
update automatically and I can't find somewhere with an easy overview of
criteria definitions) :-(

VBA function returns #NAME?
 

field:=1, Criteria1:="="

for blanks

field:=1, Criteria:="<"

for non-blanks.

--
Regards,
Tom Ogilvy




"mogens" wrote:

Chip Pearson wrote:
Where are you putting the code? It should be in a regular code
module, NOT the ThisWorkbook code module or a sheet module.


Thanks Chip - that explained the error.

I am trying to find a way to see how to qualify for instance how to
select only records with "blanks". This formula didn't give much of an
explanation though.

A shame that you are not able to switch between different criteria for
an autofilter as the advanced filter is a mystery to me (doesn's seem to
update automatically and I can't find somewhere with an easy overview of
criteria definitions) :-(