Syntax Error "nth day of month"

[richard blaine]

Can someone please correct this, thanks:

Function NthDayOfMonth(Which_Day As String, Which_Date As String, Occurence As Byte) As Date

Dim i As Integer

Dim iDay As Integer

Dim iDaysInMonth As Integer

Dim FullDateNew As Date

Dim lCount As Long



Which_Date = CDate(Which_Date)



Select Case UCase(Which_Day)

Case "SUN"

iDay = 1

Case "MON"

iDay = 2

Case "TUE"

iDay = 3

Case "WED"

iDay = 4

Case "THU"

iDay = 5

Case "FRI"

iDay = 6

Case "SAT"

iDay = 7

End Select





FullDateNew = DateSerial(Year(Which_Date), Month(Which_Date), 1)



iDaysInMonth = Day(DateAdd("d", -1, DateSerial _

(Year(Which_Date), Month(Which_Date) + 1, 1)))



For i = 0 To iDaysInMonth

If Weekday(FullDateNew + i) = iDay Then

lCount = lCount + 1

End If

If lCount = Occurence Then

NthDayOfMonth = FullDateNew + i

Exit For

End If

Next i



End Function

 

[Scott Huish]

What's it supposed to do and what problem are you having exactly?

 

[richard blaine]

It returns your desired weekday of a month (e.g. 2nd tuesday of november 2009). The cell formula:

=nthDayOfMonth("tue","11/11/2005",2)

This worked fine on my old worksheet until my computer crashed and I lost the entire file. Then when I pasted the code into the new sheet (VBA), I got a syntax error. Even after fooling with it, I get a #NAME? error.