Stuck with multi worksheet Function

[Cimjet]

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

 

[Javed]

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

 

[Cimjet]

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:AF47,""½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:AF47,""½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
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

 

[Javed]

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.

 

[Cimjet]

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:AF47,""½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: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: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
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.

 

[Javed]

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

 

[Cimjet]

Hi Javed
Yes I did. This is what I tried and I get " Run time error 1004" Popup message
is "Application-defined or Object-defined error
Sub MyMacro()
Worksheets("Calendar").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("Calendar").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 SubThanks for your helpCimjet"Javed" <[email protected]> wrote in messageHave you entered my code in standard module.Dont see any reason to put> in worksheet_change event.That is the reason for loop.

 

[Javed]

Yes correct.

If you paste the code directly in module on hyphen is being inserted
automatically.Pls check the adresses like $B$36:$AF$­­47 and you will
get it.

 

[Cimjet]

Hi Javed
Thank you for all your effort, Joeu2004 made it work for me.
All the best to you.
Cimjet

Yes correct.

If you paste the code directly in module on hyphen is being inserted
automatically.Pls check the adresses like $B$36:$AF$­­47 and you will
get it.