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.