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

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

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

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

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

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

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I should of said the full line goes red with 'OR" behing highlighted. And the
popup message.
Expected expression
Cimjet
"Cimjet" wrote in message
...
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

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

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

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On May 11, 8:34*am, "Cimjet" wrote:
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.

Cimjet wrote:
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.

In another thread, Cimjet wrote:
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.

Cimjet wrote:
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

Cimjet wrote:
For vn = 1 To Worksheets.Count
Next

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

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?

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?

I've got to go, will be back tomorrow.
Cimjet
"Cimjet" wrote in message
...
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?

Cimjet

Here is the link should you or anyone else be interested.

http://www.mcgimpsey.com/excel/removepwords.html

Mick

Hi all

Just as a kinda off-topic contribution..

Cimjet

there is an Excel file available that actually unlocks protected sheets.

the files is titled "AllInternalPasswords.xls"

Breaks worksheet and workbook structure passwords.
Bob McCormick probably originator of base code algorithm modified for
coverage of workbook structure / windows passwords and for multiple
passwords.

Norman Harker and JE McGimpsey 27-Dec-2002 (Version 1.1)
Modified 2003-Apr-04 by JEM: All msgs to constants, and eliminate one
Exit Sub (Version 1.1.1)

Reveals hashed passwords NOT original passwords


I can't remember where I downloaded it, but it has come in very handy,
especially since I have had to unlock workbooks that have been protected by
redundant employees.

HTH
Mick

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:
I suppose you have some fixed range where you put either date or V or I
And in one cell you need the total V total I etc.
and it is for 17 sheets.
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

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

"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 :-)

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