Hi Joeu2004
I feel bad not being able to explain it better, English is not my native tongue
and VBA is new to me. BUT your UDF works.
The reason I didn't want a formula in the cells is because They don't allow any
locking of cells at my wife's office and she keeps removing the formula. Please
don't ask why.
I appreciate the effort.
Regards
Cimjet





"joeu2004" wrote in message
...
On May 11, 2:35 pm, I wrote:
Why is this a SheetChange event macro?
[....]
Perhaps you are trying to emulate a volatile UDF
(VBA user-defined function).

Cimjet, does the following meet your needs?

If not, please explain why it does not. That might provide useful
insight into your requirements.

Put the following UDF into a VBA module, __not__ into a Sheet or
ThisWorkbook Excel object.

The UDF does not have to be volatile if the ranges are specified as
parameters, as intended. (But see alternative below.)

Function myCountIf(s As String, ParamArray a())
Dim r As Variant
For Each r In a
myCountIf = _
myCountIf + WorksheetFunction.CountIf(r, s)
Next
End Function

Then put the following formulas into the appropriate Excel cells:

I50: =mycountif("½V",$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF $47)
I51: =mycountif("V",$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$ 47)
I52: =mycountif("i",$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF$ 47)
I53: =mycountif("½i",$B$6:$AF$17,$B$21:$AF$32,$B$36:$AF $47)

Note that myCountIf is not limited to 3 ranges. You can have a many
as you want -- well, up to 29 in XL2003. (More in XL2007 and later.)

However, in another thread, you wrote:
I prefer not to copy the formula in the cell
but if it's the only way then ok

As I noted previously, it is unclear why you "prefer" not to copy the
formula. It is not the "only" way; but it might be the best way.

If you prefer, you can hardcode the ranges in the UDF, just as you
were doing in the SheetChange event macro.

But in that case, the UDF must be made volatile. To wit:

Function myCountIf(s As String)
Dim r As Variant
Application.Volatile
For Each r In Array("$B$6:$AF$17", "$B$21:$AF$32", "$B$36:$AF$47")
myCountIf = _
myCountIf + WorksheetFunction.CountIf(Range(r), s)
Next
End Function

However, the only advantage of the volatile UDF is that the strings do
not have to be hardcoded.

If you prefer to hardcode the strings as well, there is no advantage.

Moreover, the advantage of the SheetChange event macro over a volatile
UDF is the ability to limit when the computation is performed by
comparing with the sheet name (Sh.Name) and Target.

If you want to limit the SheetChange computation based on Target, you
need to provide more information, namely: when do you want to perform
the computation? That is, when which cells (ranges) are edited?

I am guessing only when $B$6:$AF$17, $B$21:$AF$32 and $B$36:$AF$47 are
edited. Right?