Stuck with multi function Part 2

[Cimjet]

I repost to make it more clear, I hope.
This works great.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sz As Variant, t As Long
Dim vn As Integer
For vn = 1 To Worksheets.Count
Next
For Each sz In _
Array("$B$6:$AF$17", "$B$21:$AF$32", "$B$36:$AF$47")
t = t + _
Application.WorksheetFunction.CountIf(Range(sz), "V")
Next
Range("I51") = t
End Sub
---------------------
But I need something like this :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sz As Variant, t As Long
Dim vn As Integer
For vn = 1 To Worksheets.Count
Next
For Each sz In _
Array("$B$6:$AF$17", "$B$21:$AF$32", "$B$36:$AF$47")
t = t + _
Application.WorksheetFunction.CountIf(Range(sz), "V")
tt = tt + _
Application.WorksheetFunction.CountIf(Range(sz), "½V")
s = s + _
Application.WorksheetFunction.CountIf(Range(sz), "i")
ss + ss + _
Application.WorksheetFunction.CountIf(Range(sz), "½i")
Next
Range("I51") = t
Range("I50") = tt
Range("I52") = s
Range("I53") = ss
________________
Obviously that don't work, can anyone help me with this

End Sub

 

[Cimjet]

Update:
Application.WorksheetFunction.CountIf(Range(sz), "V") +
WorksheetFunction.CountIf(Range(sz), "½V") this works, but divide by 2 like this
dos not Application.WorksheetFunction.CountIf(Range(sz), "V") +
WorksheetFunction.CountIf(Range(sz), "½V") /2
Plus I need to had 2 more Countif but not in the same cell.
Cimjet

 

[GS]

Cimjet submitted this idea :

Update:
Application.WorksheetFunction.CountIf(Range(sz), "V") +
WorksheetFunction.CountIf(Range(sz), "½V") this works, but divide by 2 like
this dos not Application.WorksheetFunction.CountIf(Range(sz), "V") +
WorksheetFunction.CountIf(Range(sz), "½V") /2
Plus I need to had 2 more Countif but not in the same cell.
Cimjet

Try a single line of COUNTIF using the OR function...

t = t + _
Application.WorksheetFunction.CountIf(Range(sz), _
OR("V","1/2V","i","1/2i"))

 

[Cimjet]

Hi Garry
I'm getting an error ( Expected Expression)
t = t + _
Application.WorksheetFunction.CountIf(Range(sz), _
OR("V","1/2V"))
The I and 1/2 I goes in a different cell.
Cimjet

 

[Cimjet]

I should of said the full line goes red with 'OR" behing highlighted. And the
popup message.
Expected expression
Cimjet

 

[GS]

I didn't test this and so really don't know that it would work. I
suspect, though, that COUNTIF only accepts 1 criteria and so is why
you'd want to iterate the string array and check for each 'condition'
separately. That means you have to repeat this for each criteria inside
your For..Each loop.

I'm a bit busy today but I'll give it a go later to see why it's not
working. Logic says if 1 COUNTIF works in the loop then so should 100
COUNTIFs.

 

[Cimjet]

I appreciate your help Garry
Thank you

 

[GS]

It's looking like the issue is the format of cell contents. Obviously
the application is treating the keyboard entry "1/2" as "½" which is
different in terms of binary value and so unexpected results are going
to occur. It might be better to copy the expected cell contents as I
did here and paste that between the double quotes in your code.

<FWIW>
I'd probably use naming that relates to the values I'm looking at so...

v1, v2, i1, i2

Range("151") = v1: Range("150") = v2
Range("152") = i1: Range("153") = i2

 

[GS]

GS explained :

It's looking like the issue is the format of cell contents. Obviously the
application is treating the keyboard entry "1/2" as "½" which is different in
terms of binary value and so unexpected results are going to occur. It might
be better to copy the expected cell contents as I did here and paste that
between the double quotes in your code.

<FWIW>
I'd probably use naming that relates to the values I'm looking at so...

v1, v2, i1, i2

Range("151") = v1: Range("150") = v2
Range("152") = i1: Range("153") = i2

Just to clarify by example...

Application.WorksheetFunction.CountIf(Range(sz), "½V")

 

[joeu2004]

I repost to make it more clear, I hope.

Please don't! By reposting, we lose the context of the original post,
which may or may not add important information.

Obviously that don't work

Why do you say it doesn't?

It seems to correctly compute the total of each of the following
separately for all 3 ranges: total "V", total "½V", total "i", total
"½i".

If that is not what you want, what exactly do you want to count?

Please explain in English. Your pseudocode might be obscuring your
intent.

but seem to get in a constant loop

Is that what you mean by "obviously that don't work"?

All you need is to add the following lines:

Application.EnableEvents = True
.....the body of your event macro....
Application.EnableEvents = False

Cimjet wrote (edited):

This works for one formula...
Range("I51").Formula = _
"=COUNTIF($B$6:$AF$17,""V"")+COUNTIF($B$21:$AF$32,""V"")" & _
"+COUNTIF($B$36:$AF$47,""V"")+(COUNTIF(B7:AF47,""½V"")/2)" [....]
P.S Just realized the last countif on the formula is no
good but will deal with that later.

Ignoring the last term per your PS, the variable t in your SheetChange
event macro seems to correctly emulate this formula.

Cimjet wrote (edited):

But I need it for more then one [....]
I need this one below also and the 2 together don't work
Range("I50").Formula = _
"=COUNTIF($B$6:$AF$17,""i"")+COUNTIF($B$21:$AF$32,""i"")" & _
"+COUNTIF($B$36:$AF$47,""i"")+(COUNTIF(B7:AF47,""½i"")/2)"

"More than one" what? Are you referring to "V", "i", "½V" and "½i"?

Why do you say the two formulas "together don't work"?

Again, ignoring the last term per your PS, the variable s in your
SheetChange event macro seems to correctly emulate this formula.

plus I prefer not to copy the formula in the cell

Why not? Are you trying to hide the calculation from the user? Or do
you have some other reason, perhaps misguided, for not copying the
formula into each of I50:I53?

Cimjet wrote (edited):

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)

Why is this a SheetChange event macro? Do you really want to redo
this computation every time any worksheet is edited?

Perhaps you are trying to emulate a volatile UDF (VBA user-defined
function). But unless you have only one worksheet, I suspect you
would prefer to do this computation only when the "Calendar" worksheet
is edited. (I am borrowing the name "Calendar" from a previous
posting that seems related.)

In that case, add the following to the beginning of the event macro:

If Sh.Name <> "Calendar" Then Exit Sub
Application.EnableEvents = True
.....the body of your event macro....
Application.EnableEvents = False

For vn = 1 To Worksheets.Count
Next

Why do you have this? It does nothing but waste time.

 

[joeu2004]

Try a single line of COUNTIF using the OR function...
t = t + _
Application.WorksheetFunction.CountIf(Range(sz), _
OR("V","1/2V","i","1/2i"))

I didn't test this and so really don't know that it
would work. I suspect, though, that COUNTIF only
accepts 1 criteria

LOL! That is only a small part of your mistakes.

First, there is no OR function in VBA.

Second, you might have been thinking of the Excel paradigm
IF(OR({"V","½V","i","½i"}),...) in mind. But note that the array
constant (list of strings) is surrounded by curly braces, not
parentheses.

Third, if that was your intent, you would want to use Evaluate
expression, not WorksheetFunction. And you would need to change some
double-quotes to pairs of double-quotes, among other changes.

Fourth, not matter! Excel COUNTIF does not support the use of the OR
function in its criterion parameter. That is, the following paradigm
does __not__ work even in Excel, if this is what you had in mind:

=COUNTIF($B$6:$AF$17,OR({"V","½V","i","½i"}))

Excel will not report an error. It is simply does not do what you
might have intended.

 

[joeu2004]

Errata....

Second, you might have been thinking of the Excel paradigm
IF(OR({"V","½V","i","½i"}),...) in mind.  But note that
the array constant (list of strings) is surrounded by curly
braces, not parentheses.

I meant to write the paradigm:

IF(OR(A1={"V","½V","i","½i"}),...)

 

[GS]

joeu2004 was thinking very hard :

LOL! That is only a small part of your mistakes.

First, there is no OR function in VBA.

That was my bad due to having an overload day. Everything related
follows that...

 

[joeu2004]

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.)

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?

 

[Cimjet]

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

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.)

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?

 

[Cimjet]

I've got to go, will be back tomorrow.
Cimjet

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

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.)

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?

 

[Cimjet]

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.
I replied to Javed on the previous post with this information:
Hi Javed
This is the proper code :
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'\\ (Using "Private Sub Workbook_SheetChange" allows me to have one macro for
all 17 sheets.)
Dim vn As Integer
For vn = 1 To Worksheets.Count ' \\ (This is for the macro to work on all
sheets)
Next
Range("I51").Formula = "=COUNTIF($B$6:$AF$17,""V"")+COUNTIF($B$21:$AF$32,""V"")
_
+COUNTIF($B$36:$AF$47,""V"")+(COUNTIF(B7:AF47,""½V"")/2)" ' \\ ( This line alone
works great.)

'Range("I50").Formula = "=COUNTIF($B$6:$AF$17,""i"")+COUNTIF($B$21:$AF$32,""i"")
_
+COUNTIF($B$36:$AF$47,""i"")+(COUNTIF(B7:AF47,""½i"")/2)"
End Sub
If I remove the formula for the Range ("I50") and leave Range ("I51") working,
it works perfectly
but with both Range it seem to go in a loop,
I need to press "Esc" to be able to continue.and I get the message "Code
execution has been interrupted.)
I just can't get both ranges to work together.
----------------------------------
To reply to your comments:That is exactly what I need. It's a Vacation planner with 12 month calendar on
each Tab, 17 Tabs, one for each Employee.
They replace the dates with the letter "V" for vacation or the letter "I" for
Illness
and at the bottom cell I50 & 51 is the total of vacation and illness.
The Tab "Calendar is the Template"
I sure hope this is clearer.
Regards
Cimjet

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.)

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?

 

[Cimjet]

Hi Garry
Thanks for your patient and this is what I posted to Joeu2004.
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.
I replied to Javed on the previous post with this information:
Hi Javed
This is the proper code :
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'\\ (Using "Private Sub Workbook_SheetChange" allows me to have one macro for
all 17 sheets.)
Dim vn As Integer
For vn = 1 To Worksheets.Count ' \\ (This is for the macro to work on all
sheets)
Next
Range("I51").Formula = "=COUNTIF($B$6:$AF$17,""V"")+COUNTIF($B$21:$AF$32,""V"")
_
+COUNTIF($B$36:$AF$47,""V"")+(COUNTIF(B7:AF47,""½V"")/2)" ' \\ ( This line alone
works great.)

'Range("I50").Formula = "=COUNTIF($B$6:$AF$17,""i"")+COUNTIF($B$21:$AF$32,""i"")
_
+COUNTIF($B$36:$AF$47,""i"")+(COUNTIF(B7:AF47,""½i"")/2)"
End Sub
If I remove the formula for the Range ("I50") and leave Range ("I51") working,
it works perfectly
but with both Range it seem to go in a loop,
I need to press "Esc" to be able to continue.and I get the message "Code
execution has been interrupted.)
I just can't get both ranges to work together.
----------------------------------
To reply to your comments:That is exactly what I need. It's a Vacation planner with 12 month calendar on
each Tab, 17 Tabs, one for each Employee.
They replace the dates with the letter "V" for vacation or the letter "I" for
Illness
and at the bottom cell I50 & 51 is the total of vacation and illness.
The Tab "Calendar is the Template"
I sure hope this is clearer.
Regards
Cimjet

 

[Clif McIrvin]

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.)

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?

 

[Cimjet]

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

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.)

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?