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

I appreciate your help Garry
Thank you
"GS" wrote in message ...
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

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

--
Garry

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

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

--
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, 11:09 am, GS wrote:
Try a single line of COUNTIF using the OR function...
t = t + _
Application.WorksheetFunction.CountIf(Range(sz), _
OR("V","1/2V","i","1/2i"))

On May 11, 2:06*pm, GS wrote:
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.

Errata....

On May 11, 3:03*pm, joeu2004 wrote:
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"}),...)

joeu2004 was thinking very hard :
On May 11, 11:09 am, GS wrote:
Try a single line of COUNTIF using the OR function...
t = t + _
Application.WorksheetFunction.CountIf(Range(sz), _
OR("V","1/2V","i","1/2i"))

On May 11, 2:06*pm, GS wrote:
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.

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


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.

--
Garry

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

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?

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?

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

"GS" wrote in message ...
joeu2004 was thinking very hard :
On May 11, 11:09 am, GS wrote:
Try a single line of COUNTIF using the OR function...
t = t + _
Application.WorksheetFunction.CountIf(Range(sz), _
OR("V","1/2V","i","1/2i"))

On May 11, 2:06 pm, GS wrote:
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.

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


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.

--
Garry

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

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

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

On May 12, 12:40*pm, "Cimjet" wrote:
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.

Yes. That is a "good practice". If you do not have a public file-
sharing website to use, see the list at the bottom. Be sure the file
is "shared" or "sharable". It would be best if you tested the URL
("link") by logging out of the website and simply entering the URL
into a browser to be sure that we will be able to download the file.

For my benefit, it would be best if you uploaded an XL2003-compatible
file. But if that is too much trouble, upload a normal file. If I
cannot open it, someone else probably can.

However, you still need to answer a critical question: when is it
necessary to recompute I50:I53? Only when a cell changes in the
ranges $B$6:$AF$17, $B$21:$AF$32 and $B$36:$AF$47?

Cimjet wrote elsewhe
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 believe I answered that, although I would expect that you would get
an infinite loop with either Range assignment.

If you use a SheetChange event macro, you need to disable events in
the macro so that the assignment to a range within the macro does not
trigger another event.

Cimjet wrote elsewhe
I'm not sure what is a Volatile UDF.
I know what is a UDF

A volatile UDF (user-defined function) is a VBA function that is
executed each time any cell in the workbook is modified by some user
operation (editing, sorting, deleting, etc). It is VBA function that
starts with Application.Volatile.

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

That's an operational problem you and your management should try to
fix. When an employee, contractor or volunteer leaves, part of the
exit procedure should be to sit down with them and be sure that all
critical files are usable without them.

Usually, it is not difficult to "break" Excel's cell protection
mechanism. Some procedures are described on websites. Search for
"excel cell protection removal" without quotes.

Some free file-sharing websites that people have suggested or used....

Windows Live Skydrive: http://skydrive.live.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidSha http://www.rapidshare.com
Box.Net: http://www.box.net/files

I use bot.net myself.

"joeu2004" wrote in message
...
On May 12, 12:40 pm, "Cimjet" wrote:
[ ]
However, you still need to answer a critical question: when is it
necessary to recompute I50:I53? Only when a cell changes in the
ranges $B$6:$AF$17, $B$21:$AF$32 and $B$36:$AF$47?


"Cimjet" wrote in message
...
Hi Clif
Let me try to answer those questions.
[ ]
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



--
Clif McIrvin

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

Hi Joeu2004
To answer your questions.
"Yes" Only when a cell changes in the
ranges $B$6:$AF$17, $B$21:$AF$32 and $B$36:$AF$47? but not critical if it makes
any different.
I'm using XL2003.
I'm retired, its my wife's office and the people that set rules are not always
the "sharpest tool in the shed" if you know what I mean.
This is the link; http://cjoint.com/?AEmvV7hxz6s
You'll see all the different macro I tried.
Thanks
Cimjet

"joeu2004" wrote in message
...
On May 12, 12:40 pm, "Cimjet" wrote:
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.

Yes. That is a "good practice". If you do not have a public file-
sharing website to use, see the list at the bottom. Be sure the file
is "shared" or "sharable". It would be best if you tested the URL
("link") by logging out of the website and simply entering the URL
into a browser to be sure that we will be able to download the file.

For my benefit, it would be best if you uploaded an XL2003-compatible
file. But if that is too much trouble, upload a normal file. If I
cannot open it, someone else probably can.

However, you still need to answer a critical question: when is it
necessary to recompute I50:I53? Only when a cell changes in the
ranges $B$6:$AF$17, $B$21:$AF$32 and $B$36:$AF$47?

Cimjet wrote elsewhe
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 believe I answered that, although I would expect that you would get
an infinite loop with either Range assignment.

If you use a SheetChange event macro, you need to disable events in
the macro so that the assignment to a range within the macro does not
trigger another event.

Cimjet wrote elsewhe
I'm not sure what is a Volatile UDF.
I know what is a UDF

A volatile UDF (user-defined function) is a VBA function that is
executed each time any cell in the workbook is modified by some user
operation (editing, sorting, deleting, etc). It is VBA function that
starts with Application.Volatile.

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

That's an operational problem you and your management should try to
fix. When an employee, contractor or volunteer leaves, part of the
exit procedure should be to sit down with them and be sure that all
critical files are usable without them.

Usually, it is not difficult to "break" Excel's cell protection
mechanism. Some procedures are described on websites. Search for
"excel cell protection removal" without quotes.

Some free file-sharing websites that people have suggested or used....

Windows Live Skydrive: http://skydrive.live.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidSha http://www.rapidshare.com
Box.Net: http://www.box.net/files

I use bot.net myself.

On May 12, 3:11*pm, "Clif McIrvin" wrote:
"joeu2004" wrote in message
However, you still need to answer a critical question: *when is it
necessary to recompute I50:I53?
[....]
"Cimjet" wrote in message
[....]
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

Thanks, Clif. "Couldn't see the forest for the trees" ;-). My bad!

On May 12, 3:22*pm, "Cimjet" wrote:
This is the link; *http://cjoint.com/?AEmvV7hxz6s

See my modified version at http://www.box.net/shared/x1r3a1mv03 .

Note: You can use that file as-is. But for the final "product", I
suggest that you copy the text of my macros and paste them into your
file.

Reason: When I saved the modified file on my system, I got some
warnings. I did not bother to think about the consequences of those
warnings, if any.

My point is: my file might not be identical to yours in all
respects. It would be better to use it as a prototype, not a final
"product".

My modified file contains 3 macros:

Workbook_SheetChange in ThisWorkbook:
- simply calls fillFormulas

fillFormulas in Module1:
- does all the work

chkFormulas in Module1:
- calls fillFormulas for all appropriate worksheets.
- "appropriate" means: I49 contains the word "Recorded".

If you choose to use chkFormulas, the SheetChange event macro might
not be needed. That's a judgment call -- yours.

The SheetChange event macro puts the formulas into only the worksheet
that caused the event -- that is, the changed worksheet -- if
necessary.

I put very few comments into the macros, in part to make them concise
and readable. I am hoping that the programming is clear.

If you have any questions, you can write to me at joeu2004 "at"
hotmail.com.

Essentially, fillFormulas put the appropriate formulas into I50:I54.
It is best to let Excel perform the computation, if possible.

When the formulas are in place, the SheetChange event macro will do
almost nothing. That makes the overhead of the event macro very
little.

Note that I guessed at what is an "appropriate" formula. I hope I got
it right. For each of I50:I54, I count the number of "I" for I50, "V"
for I51, "P" for I52 etc. I also count 1/2 the number of "½I" for
I50, "½V" for I51, "½P" for I52 etc.

Thus, for example, the formula in I50 becomes:

=COUNTIF(B6:AF17,"I")+COUNTIF(B6:AF17,"I")
+COUNTIF(B21:AF32,"I")+COUNTIF(B36:AF47,"I")
+ (COUNTIF(B6:AF17,"½I")+COUNTIF(B6:AF17,"½I")
+COUNTIF(B21:AF32,"½I")+COUNTIF(B36:AF47,"½I"))/2

I hope I guessed your intentions correctly. If not, send me the
correct formula to use for each of I50:I54, and I will make the
necessary change if you cannot do it yourself.

Hope this is a step in the right direction. Please let me know one
way or the other (email or a reply here).

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

Cimjet

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

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

Mick

Thank you Vacuum Sealed

"Vacuum Sealed" wrote in message
ond.com...
Cimjet

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

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

Mick