Vlookup Error Using Dates

[Ark68]

I am getting an "Unable to get the Vlookup property of the WorksheetFunction class " error with the line in red below. This code opens the file (2021) Sunset.xlsx from the specified path and applies a vblookup function to compare the value tm (a true date) to the values in column 1 of the worksheet Sunset in the file opened by GetObject. Column 1 contains true date values. In testing for example, tm = 2021-06-20. There is a match at cell A112 (2020-06-20)

With GetObject("D:/WSOP 2020/SupportData/(2021)Sunset.xlsx")
     Set Range1 = .Sheets("Sunset").Range("A:D")
     tm = ws_cdata.Cells(pebftp, 1).Value
     ssoff = Application.WorksheetFunction.VLookup(tm, Range1, 4, False)
     .Close
End With

 

[EFANYoutube]

Does this work?

Set Range1 = .Sheets("Sunset").Range("A:A")
tm = ws_cdata.Cells(pebftp, 1).Value
ssoff  = Range1.Find(tm).Offset(,3).value
.Close

 

[Ark68]

Thank you!! It appears to be working. Is this a reliable alternative to using vlookup? Any idea why the vlookup failed?

 

[EFANYoutube]

Using a worksheet function in vba should be a last resort, there are many better ways of doing things with the built in functions of vba as they are designed to be used with it
Sorry that I don't know what is wrong with your VLookUp but this is the prefered method to replace it in vba

 

[Ark68]

That's good to know. I should have figured that there was more than one way to do things in Excel. I guess I need to understand the difference between worksheet functions and vba functions.