Hi Javed
This is the proper code :
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim vn As Integer
For vn = 1 To Worksheets.Count
Next
Range("I51").Formula =
"=COUNTIF($B$6:$AF$17,""V"")+COUNTIF($B$21:$AF$32, ""V"")+COUNTIF($B$36:$AF$47,""V"")+(COUNTIF(B7:AF4 7,""½V"")/2)"

'Range("I50").Formula =
"=COUNTIF($B$6:$AF$17,""i"")+COUNTIF($B$21:$AF$32, ""i"")+COUNTIF($B$36:$AF$47,""i"")+(COUNTIF(B7:AF4 7,""½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"
Regards
Cimjet
"Javed" wrote in message
...
I suppose you have some fixed range where you put either date or V or
I.And in once cell you need the total V total I etc.
and it is for 17 sheets.

the below solution will put required formula in 2 cell .( I51 & I50).


Sub MyMacro

Dim vn As Integer

For vn = 1 To Worksheets.Count

worksheets(vn).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)"

worksheets(vn).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)"

Next vn
End Sub

If not clear you may send the file.