phendrena
06-29-2009, 03:03 AM
Hi there,
I have the following code :-
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
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,
I have the following code :-
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
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,