Solved: VBA VLookup Problem (Excel '97)

[phendrena]

Hi there,

I have the following code :-
[vba]Private Sub sSupportRota()
Dim dtDate As Date
Dim dtToday As Date
Dim intMonth As Integer
Dim strRange As String
Dim ws As Worksheet
Set ws = Worksheets("Support")
dtToday = Date
dtDate = Now
intMonth = DatePart("m", dtDate)
Select Case intMonth
Case 1: strRange = "JanSupport"
Case 2: strRange = "FebSupport"
Case 3: strRange = "MarSupport"
Case 4: strRange = "AprSupport"
Case 5: strRange = "MaySupport"
Case 6: strRange = "JunSupport"
Case 7: strRange = "JulSupport"
Case 8: strRange = "AugSupport"
Case 9: strRange = "SepSupport"
Case 10: strRange = "OctSupport"
Case 11: strRange = "NovSupport"
Case 12: strRange = "DecSupport"
End Select
ws.Range("B44") = Application.WorksheetFunction.VLookup(dtToday, ws.Range(strRange), 2, False)
End Sub[/vba]

I am getting the following error :
"Run-time error 1004
Unable to get the VLookup property of the WorksheetFunction class"

Would anyone please be able to advise where i am going wrong.

Thanks,

[mdmackillop]

Dim your dates as long
[VBA]
Private Sub sSupportRota()
Dim dtDate As Long
Dim dtToday As Long

[/VBA]

[phendrena]

Hi,

Thanks for the reply.
I've just tried that and i'm still getting the same error.
The dates on the worksheet are formatted as dd/mm/yyyy.
I've tried to change them to a standard excel format with no success.

[mdmackillop]

I'm using the same format. Here's my test file.

[phendrena]

Thanks.

I now feel really daft, as i've also discovered i'm using the wrong names ranges - should 'rota' and not 'support'.
Making the changes to the dates and the named ranges now produce the result i'm looking for!

Cheers.