If I post a link to the file in question would someone have a look at it and see
if they can make it work.
Regards
Cimjet


"Cimjet" wrote in message
...
Hi Clif
Let me try to answer those questions.
Perhaps you are trying to emulate a volatile UDF
I'm not sure what is a Volatile UDF. I know what is a UDF ans I can't use that
because the problem is that they keep removing the formula in the cells and
they don't want lock cells (No Protection ) Too many people protecting
worksheets then moving away and no longer can work with the file.
I Prefer not to copy the formula in the cell
but if it's the only way then ok.
It's ok if the formula returns by itself, that's one reason why I use the
Sheet-change.
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.
I'm lost with this, I don't know enough about UDF mainly "Volatile UDF"
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?
The answer is "Yes
------------------------
My last post with Javed is possibly the answer if we can run the formula for
two different cells e.g. "I50 & I51"
Thank you for your help
Regards
Cimjet

"Clif McIrvin" wrote in message
...
"Cimjet" wrote in message
...
Hi Joeu2004
I appreciate your patient with me and will start over with one code.
It gets to confusing with all the codes we have tried.

Cimjet ...

First, I appreciate that you are working in two unfamiliar languages at the
same time: English *and* VBA.

If you noticed, Javed and joeu2004 both mentioned that using the
WorksheetChange event is causing you trouble.

I encourage you to work your way through joeu2004's list of questions (I
included them below) and answer them in-line. When you understand his
questions you will have learned much more about working with VBA; and when
you answer his questions it will be much easier for us to help you.

Keeping the questions and answers together in the reply makes it much easier
for everyone to keep track of the details.


[ ]

"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?



--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)