Make sure the code is in a general module, not a sheet module, the
thisworkbook module or a userform module. in the VBE, Insert=Module. Put
it in that.
--
Regards,
Tom Ogilvy
"nougain" wrote in
message ...
Compilation error disappear, but when I use ss_weekly function it shows
#NAME? in my sheet's cell. I don't know where I should focus to correct
it. Here is the updated VBA code:
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
' ss: Schedule Slippage
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''
Function ss_weekly(endDate As Date) As Double
' Data range
Dim dateRange As range
Dim dataRange As range
'CSR is sheet name
Set dateRange = Sheets("CSR").range("O20:O351")
Set dataRange = Sheets("CSR").range("AA20:AA351")
' Local variables to compute the average
Dim sumDataForAllDatesLessThanGivenDate As Double
Dim sumDataForAllDatesLessThanOneWeekBack As Double
Dim countDataForAllDatesLessThanGivenDate As Integer
Dim countDataForAllDatesLessThanOneWeekBack As Integer
Dim sumData As Double
Dim countData As Integer
' Input endDate is expected to be a Friday date. Therefor adding 2
days to get the Sunday and
' subtracting 4 to get the last Monday
sumDataForAllDatesLessThanGivenDate = Application.SUMIF(dateRange,
"<=" & (endDate + 2), dataRange) 'On Sunday
sumDataForAllDatesLessThanOneWeekBack =
Application.SUMIF(dateRange, "<" & (endDate - 4), dataRange) 'On
just passed Monday
countDataForAllDatesLessThanGivenDate =
Application.CountIf(dateRange, "<=" & (endDate + 2))
countDataForAllDatesLessThanOneWeekBack =
Application.CountIf(dateRange, "<" & (endDate - 4))
' Slippage
sumData = sumDataForAllDatesLessThanGivenDate -
sumDataForAllDatesLessThanOneWeekBack
countData = countDataForAllDatesLessThanGivenDate -
countDataForAllDatesLessThanOneWeekBack
If countData <= 0 Then
ss_weekly = 0
Else
ss_weekly = sumData / countData
End If
End Function
--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=517826