Hello,
I'm trying to build custom function that will result an array with rows that match specific criteria.
Function takes Worksheet, Criteria_Ranges as String, Criteria_Values as String.
Criteria_Ranges = "1,2,3,4,5,6" - these are columns to search
Criteria_Values = "4,AA,BB,CC,DD,EE" - criteria for columns
This means: count if record had 4 in column 1, AA in Column 2, ...
Since number of pairs Criteria_Range / Criteria_Value is variable I cannot set it for Application.Countifs
I tried building String and then passing to Application.Countifs but it returns error Invalid Number of Arguments, which would mean that String is not "extracted" (which kinda makes sense).
Any ideas how to "extract" then string into Application.Countifs or any other Alternate ideas?
One more would be evaluate string but somehow I'm not convinced.
Best Regards,
Barb
I'm trying to build custom function that will result an array with rows that match specific criteria.
Function takes Worksheet, Criteria_Ranges as String, Criteria_Values as String.
Criteria_Ranges = "1,2,3,4,5,6" - these are columns to search
Criteria_Values = "4,AA,BB,CC,DD,EE" - criteria for columns
This means: count if record had 4 in column 1, AA in Column 2, ...
Since number of pairs Criteria_Range / Criteria_Value is variable I cannot set it for Application.Countifs
I tried building String and then passing to Application.Countifs but it returns error Invalid Number of Arguments, which would mean that String is not "extracted" (which kinda makes sense).
Any ideas how to "extract" then string into Application.Countifs or any other Alternate ideas?
One more would be evaluate string but somehow I'm not convinced.
Code:
Public Function SearchMultipleCriteria2(Target_Worksheet As Worksheet, Criteria_Ranges As String, Criteria_Values As String) As Variant
Dim Criteria_Ranges_Array As Variant
Dim Criteria_Values_Array As Variant
Dim Criteria_Range_Counter As Integer
Dim Criteria_Range_String As String
Dim Countifs_String As String
Dim Target_LastRow As Long
Criteria_Ranges_Array = VBA.Split(Criteria_Ranges, ",")
Criteria_Values_Array = VBA.Split(Criteria_Values, ",")
If UBound(Criteria_Ranges_Array) = UBound(Criteria_Values_Array) Then
With Target_Worksheet
Target_LastRow = .Cells(.Rows.Count, VBA.CInt(Criteria_Ranges_Array(0))).End(xlUp).Row
For Criteria_Range_Counter = LBound(Criteria_Ranges_Array) To UBound(Criteria_Ranges_Array)
Criteria_Range_String = ".Range(.Cells(2," & Criteria_Ranges_Array(Criteria_Range_Counter) & "),.Cells(" & Target_LastRow & "," & Criteria_Ranges_Array(Criteria_Range_Counter) & "))"
If Countifs_String = "" Then
Countifs_String = "Criteria_Values_Array(" & Criteria_Range_Counter & ")" & ", " & Criteria_Range_String
Else
Countifs_String = Countifs_String & ", " & "Criteria_Values_Array(" & Criteria_Range_Counter & ")" & ", " & Criteria_Range_String
End If
Next Criteria_Range_Counter
SearchMultipleCriteria2 = Application.CountIfs(Countifs_String)
End With
Else
Err.Raise Number:=5, Description:="Number of Criteria_Ranges is different than Number of Criteria_Values"
End If
End Function
Best Regards,
Barb