Hi Everyone
What I've got is a 12 month vacation planner and the dates are replace with the
letter V for vacation or I for illness, plus they count half days.
I'm replacing this formula.
=COUNTIF($B$6:$AF$17,"V")+COUNTIF($B$21:$AF$32,"V" )+COUNTIF($B$36:$AF$47,"V")+(COUNTIF(B7:AF47,"½
v")/2)
With this macro, but need to do it for half days and for ( I ) Illness and on 17
w.sheets
I try different things but seem to get in a constant loop and I need your help.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sz As Variant, t As Long
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
End Sub
Regards
Cimjet

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

The above code must be in one line.In this post it broke in 2 lines.

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

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

End Sub

Hi Javed
Thank you for your help.
This works for one formula...
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
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)"
/// But I need it for more then one and it wont work, plus I prefer not to copy
the formula in the cell but if it's the only way then ok. 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:AF4 7,""½i"")/2)"
End Sub
P.S Just realized the last countif on the formula is no good but will deal with
that later.
Regards
Cimjet
"Javed" wrote in message
...
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)"

The above code must be in one line.In this post it broke in 2 lines.

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

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

End Sub

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.

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.

Have you entered my code in standard module.Dont see any reason to put
in worksheet_change event.That is the reason for loop.