I have 2 Sheets in the Workbook. Where Sheets("Data") contains 3 Columns picture is attached below.
then i have Sheets("Return Result") contains data picture attahced below
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
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