C
Cimjet
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
if they can make it work.
Regards
Cimjet
Cimjet said:Hi Clif
Let me try to answer those questions.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.It's ok if the formula returns by itself, that's one reason why I use the
Sheet-change.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 said:Cimjet said: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.
[ ]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?