Pass variables into vlookup worksheet function in VBA Sub

[AlexB123]

Hi,

I have a work project that uses dates of a broadcast calendar to determine month and quarter throughout the year. My solution was to hardcode the data into a spreadsheet within the macro workbook, and then use functions to get the data I need to drive my macro. However, I keep receiving "Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class." The values (today's date, last friday, etc are in the table), which looks like:

Date	Year	WeekNum	Monthname
12/27/21	2022	1	January
12/28/21	2022	1	January
12/29/21	2022	1	January
12/30/21	2022	1	January
12/31/21	2022	1	January
01/01/22	2022	1	January
01/02/22	2022	1	January
01/03/22	2022	2	January

Here is the snippet of my VBA where the error is occuring:

'Bind macro workbook Object
        Set macroWB = ThisWorkbook
            Set macroWS = macroWB.Worksheets("BROADCAST_MONTH_1")
    
    'Find current date and Broadcast Week information
                    Dim ct_DATE As Date: ct_DATE = Format(Date, "mm/dd/yy")
                    Dim iWeekday As Integer: iWeekday = Weekday(Now(), vbFriday)
                    Dim LastFridayDate As Date: LastFridayDate = Format(Now - (iWeekday - 1), "mm.dd.yy")
                    Dim weekInt As String: weekInt = WorksheetFunction.VLookup(ct_DATE, macroWS.Range("A:E"), 3, 0)
                    Dim lastFriWeekInt As String: lastFriWeekInt = Application.WorksheetFunction.VLookup(LastFridayDate, macroWS.Range("A:E"), 3, 0)
                    Dim monthName As String: monthName = Application.WorksheetFunction.VLookup(ct_DATE, macroWS.Range("A:E"), 4, 0)
                    Dim lastFriMonthName As String: lastFriMonthName = Application.WorksheetFunction.VLookup(LastFridayDate, macroWS.Range("A:E"), 4, 0)
                    Dim qtrName As String: qtrName = Application.WorksheetFunction.VLookup(ct_DATE, macroWS.Range("A:E"), 5, 0)
                    Dim lastFriQtrName As String: lastFriQtrName = Application.WorksheetFunction.VLookup(LastFridayDate, macroWS.Range("A:E"), 5, 0)

I have tested all of the attempts to use VLookup in the above lines. All fail with the same error.

Thanks for any help or suggestions!

 

[Colo]

It's a tricky error. Case to specify the date as a search value, Date type cannot be used, it must be a Long type for "WorksheetFunction.VLookup". I had struggled with the same issue a long time ago. Even now, I have no idea how come it only accepts a Long type. But just take it as "WorksheetFunction.VLookup" is made like this. For other WorksheetFunctions, the Date type works without any problems.

So just change the declaration of the variable ct_DATE as follows. Hope this helps.

Dim ct_DATE As Long: ct_DATE = Date

 

[Colo]

Oh, other Date type variables must be changed as well.