Hi All
I need a bit of help.
I'm developing some code to count the number of rows that meet one or a combination of conditions
The following code in the subTestCode procedure demonstrates two methods of populating an array with the details required to use the COUNTIFS function in the fncGetCount function.
Approach 2 works and approach 1 does not work. I need to use approach 1.
The line generating the 424 Object Required error is shown in the the fncGetCount function.
The subTestCode procedure is only to demonstrate the problem to you.
I only use one approach at a time and I comment the other one out.
Development is only in its early stages so appropriate error checking is yet to come.
Can anybody point me in the right direction?
Thanks
I need a bit of help.
I'm developing some code to count the number of rows that meet one or a combination of conditions
The following code in the subTestCode procedure demonstrates two methods of populating an array with the details required to use the COUNTIFS function in the fncGetCount function.
Approach 2 works and approach 1 does not work. I need to use approach 1.
The line generating the 424 Object Required error is shown in the the fncGetCount function.
The subTestCode procedure is only to demonstrate the problem to you.
I only use one approach at a time and I comment the other one out.
Development is only in its early stages so appropriate error checking is yet to come.
Can anybody point me in the right direction?
Thanks
VBA Code:
Private Sub subTestCode()
Dim arrParams() As Variant
Dim intCount As Long
' Approach 1
ReDim arrParams(6)
arrParams(1) = Range("B2:B1001")
arrParams(2) = "South"
arrParams(3) = Range("C2:C1001")
arrParams(4) = "Grapes"
arrParams(5) = Range("D2:D1001")
arrParams(6) = ">20"
' Approach 2
arrParams = Array(Range("B2:B1001"), "South", Range("C2:C1001"), "Grapes", Range("D2:D1001"), ">20")
intCount = fncGetCount(arrParams)
End Sub
Public Function fncGetCount(ByVal args As Variant)
Dim strString As String
Dim n As Integer
For n = LBound(args) To UBound(args) Step 2
' Error produced on this line with this section of code: args(n).Address(False, False)
strString = strString & IIf(n > 0, ",", "") & args(n).Address(False, False) & ","
Select Case TypeName(args(n + 1))
Case "Range":
strString = strString & args(n + 1).Address(False, False)
Case "Integer", "Long", "Double":
strString = strString & args(n + 1)
Case "String":
strString = strString & Chr(34) & args(n + 1) & Chr(34)
End Select
Next n
fncGetCount = Application.Evaluate("COUNTIFS(" & Mid(strString, 2) & ")")
End Function