What is wrong with this UDF?

[PATSYS]

It is supposed to return the criteria applied (if there is any) on the autofilter header.

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 = x1And Then
    
    strCri2 = " AND " & .Criteria2

    ElseIf .Operator = x1Or Then

    strCri2 = " OR " & .Criteria2

    End If

    End With

End With

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

End Function

For some reasons, it doesn't.

Thanks

 

[veyselemre]

x1Or ==> xLOr
x1And ==> xLAnd

 

[Richard Schollar]

Hi

Your problem is with xlAnd and xlOr wheere you have used x1And and x1Or. Rewrite to:

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

 

[Ivan F Moala]

It is supposed to return the criteria applied (if there is any) on the autofilter header.

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 = x1And Then
    
    strCri2 = " AND " & .Criteria2

    ElseIf .Operator = x1Or Then

    strCri2 = " OR " & .Criteria2

    End If

    End With

End With

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

End Function

For some reasons, it doesn't.

Thanks

This worked for me

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.Address) & ": " & strCri1 & strCri2
End Function

 

[PATSYS]

Veyselemre, Richard and Ivan - thank you!

How silly of me... :x