Hi K. Pong

Try:

with ThisWorkbook.Sheets("Criteria")
..Range("A1") = group ' Function exits at this line worksheet
..Range("B1") = status

end with

You could also try putting on error resume next at thestart of the sub.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"pongthai" wrote:

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