excelvbafilterconditional-statementsrange

Filter UDF Function stop working on Dates


I have 2 Sheets in the Workbook. Where Sheets("Data") contains 3 Columns picture is attached below.

enter image description here

then i have Sheets("Return Result") contains data picture attahced below

enter image description here

Now i am using below Filter UDF to return the Column A from Sheet("Data") values those matched based on Column B and C from Sheet("Data") but its not working i hope someone can look at this and help to figure out the problem.

=FILTER_AK(Data!A:A,(Data!B:B='Return Result'!B2)*(Data!C:C='Return Result'!A2))




Function FILTER_AK(Where As Range, Criteria1 As Range, Criteria2 As Range, Optional If_Empty) As Variant
    Dim Data, Result
    Dim i As Long, j As Long, k As Long
    Dim RowCount As Long
    
    ' Create space for the output (assuming the output range is the same size as input cells)
    ReDim Result(1 To Where.Rows.Count, 1 To Where.Columns.Count)
    
    ' Clear
    For i = 1 To UBound(Result)
        For j = 1 To UBound(Result, 2)
            Result(i, j) = ""
        Next j
    Next i
    
    ' Count the rows to show
    RowCount = 0
    For i = 1 To UBound(Criteria1)
        If Criteria1(i, 1) And Criteria2(i, 1) Then
            RowCount = RowCount + 1
        End If
    Next i
    
    ' Empty?
    If RowCount < 1 Then
        If IsMissing(If_Empty) Then
            Result(1, 1) = CVErr(xlErrNull)
        Else
            Result(1, 1) = If_Empty
        End If
        GoTo ExitPoint
    End If
    
    ' Get all data
    Data = Where.Value
    
    ' Copy the rows to show
    k = 0
    For i = 1 To UBound(Data)
        If Criteria1(i, 1) And Criteria2(i, 1) Then
            k = k + 1
            For j = 1 To UBound(Data, 2)
                Result(k, j) = Data(i, j)
            Next j
        End If
    Next i
    
    ' Return the result
ExitPoint:
    FILTER_AK = Result
End Function

Solution

  • Pls. try this.

    The problem that in the function call the criteria is an array. Therefore in the function it must be a Variant. The function is an array function need use CTRL+SHIFT+ENTER to set it in the cell.

    Function FILTERAKX(Where As Range, Criteria1 As Variant, Optional If_Empty) As Variant
        Dim Data, Result
        Dim i As Long, j As Long, k As Long
        Dim RowCount As Long
        
        ' Create space for the output (assuming the output range is the same size as input cells)
        ReDim Result(1 To Where.Rows.Count, 1 To Where.Columns.Count)
        ' Clear
        For i = 1 To UBound(Result)
            For j = 1 To UBound(Result, 2)
                Result(i, j) = ""
            Next j
        Next i
        
        ' Count the rows to show
        RowCount = 0
        For i = 1 To UBound(Criteria1)
            If Criteria1(i, 1) Then
                RowCount = RowCount + 1
            End If
        Next i
        
        ' Empty?
        If RowCount < 1 Then
            If IsMissing(If_Empty) Then
                Result(1, 1) = CVErr(xlErrNull)
            Else
                Result(1, 1) = If_Empty
            End If
            GoTo ExitPoint
        End If
        
        ' Get all data
        Data = Where.Value
        
        ' Copy the rows to show
        k = 0
        For i = 1 To UBound(Data)
            If Criteria1(i, 1) Then
                k = k + 1
                For j = 1 To UBound(Data, 2)
                    Result(k, j) = Data(i, j)
                Next j
            End If
        Next i
        
        ' Return the result
    ExitPoint:
        FILTERAKX = Result
    End Function