You really should use Option Explicit in your code, you are declaring one
variable and then trying to use two others in its place, won't work.

This works

Function CountCustomers(group As String, status As String) As Integer
Dim cr As Range ' Criteria Range
Dim Result As Integer

Result = 0

Set cr = ThisWorkbook.Sheets("Criteria").Range("A1:A2")
cr.Range("A1") = group ' Function exits at this line when used in
Worksheet
cr.Range("A2") = status

For Each s In ThisWorkbook.Sheets
If InStr(s.Name, "CUST") = 1 Then
Result = Result + _
Application.WorksheetFunction.DCountA(s.Range("M8: M11"), "Name", cr)
End If
Next
CountCustomers = Result
End Function



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"pongthai" wrote in message
...
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