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