Hi,

Am Thu, 6 Mar 2014 18:31:05 -0800 (PST) schrieb :

Function TotalCC(Week As String, Optional ShiftNum As Variant) As Integer

change it to
Function TotalCC(Week As String, Optional ShiftNum As Integer) As
Integer
'Application.Volatile

'item ranges based on sheet name
Set ProductRange = Sheets(WeekNum).Range("A1:A100")

change the line above to
Set ProductRange = Sheets(Week).Range("A1:A100")

'check for shiftnum argument
If IsMissing(ShiftNum) Then
'do some stuff - in this case, the shift argument is present
ElseIf Not IsMissing(ShiftNum) Then
For Each ProductCell In ProductRange
If Mid(ProductCell.Offset(0, 1).Value, 4, 1) * 1 = ShiftNum * 1 Then

and change the part above to

'check for shiftnum argument
If ShiftNum = 0 Then
'do some stuff - in this case, the shift argument is present
ElseIf ShiftNum 0 And ShiftNum < 4 Then
For Each ProductCell In ProductRange
If Mid(ProductCell.Offset(0, 1).Value, 4, 1) * 1 = ShiftNum Then


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2