P
pongthai
Hi All,
I'm writing a function that counts the number of rows that match a composite
criteria. A set of criteria is passed in the function's arguments. See
example:
Function CountCustomers(group as string, status as string) as Integer
dim cr as Range ' Criteria Range
dim Result as Integer
Result = 0
set r = ThisWorkbook.Sheets("Criteria").Range("A1:B2")
r.Range("A1") = group ' Function exits at this line when used in
worksheet
r.Range("B1") = status
For Each s In ThisWorkbook.Sheets
If InStr(s.Name, "CUST")=1 Then
result = result +
WorkSheetFunction.DCountA(s.Range("M8:M11"), "Name", criteriaRange)
End If
Next
CountCustomers = result
End Function
I created a hidden sheet "Criteria" to be used as a selection criteria for
the DCountA function (last argument).
But when I use "=CountCustomer" in the Excel sheet cell, it appeared as
'#Value'. I know that this happens when we try to modify cells in a function.
Is there any way to make the calculation go through without premature
exiting at the remarked point?
Thanks a lot in advance
pongthai
I'm writing a function that counts the number of rows that match a composite
criteria. A set of criteria is passed in the function's arguments. See
example:
Function CountCustomers(group as string, status as string) as Integer
dim cr as Range ' Criteria Range
dim Result as Integer
Result = 0
set r = ThisWorkbook.Sheets("Criteria").Range("A1:B2")
r.Range("A1") = group ' Function exits at this line when used in
worksheet
r.Range("B1") = status
For Each s In ThisWorkbook.Sheets
If InStr(s.Name, "CUST")=1 Then
result = result +
WorkSheetFunction.DCountA(s.Range("M8:M11"), "Name", criteriaRange)
End If
Next
CountCustomers = result
End Function
I created a hidden sheet "Criteria" to be used as a selection criteria for
the DCountA function (last argument).
But when I use "=CountCustomer" in the Excel sheet cell, it appeared as
'#Value'. I know that this happens when we try to modify cells in a function.
Is there any way to make the calculation go through without premature
exiting at the remarked point?
Thanks a lot in advance
pongthai