VBA Application.Countifs - variable number of criteria ranges/criteria

Barbatian

New Member
Joined
Sep 12, 2014
Messages
25
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.

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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top