Date VBA Lookup

[jlclyde]

I am trying to use Vlookup as intended by excel and find the closest
match to today. In K coloumn I have dates that are sorted ascending.
In M I have the value I woudl liek to return. Neither of these work.
Any help woudl be appreciated. Code Below.

Thanks,
Jay

Set Fn = Application.WorksheetFunction
Qty = Fn.VLookup(DateSerial(Year(Date), Month(Date), Day(Date)), Range
("K2:M7"), 3)
Qty = Fn.VLookup(Date, Range("K2:M7"), 3)

 

[smartin]

I am trying to use Vlookup as intended by excel and find the closest
match to today. In K coloumn I have dates that are sorted ascending.
In M I have the value I woudl liek to return. Neither of these work.
Any help woudl be appreciated. Code Below.

Thanks,
Jay

Set Fn = Application.WorksheetFunction
Qty = Fn.VLookup(DateSerial(Year(Date), Month(Date), Day(Date)), Range
("K2:M7"), 3)
Qty = Fn.VLookup(Date, Range("K2:M7"), 3)

The following works for me when today's date is in K2:

Sub VLDate()
Dim Fn As Object
Dim Qty As Variant
Dim MyDate As Long
Set Fn = Application.WorksheetFunction
MyDate = CLng(DateSerial(2009, 10, 21))
Qty = Fn.VLookup(MyDate, Range("K2:M7"), 3, False)
Debug.Print Qty
End Sub

Two things are going on here. First, if the VLOOKUP fails to find a
match, an "application defined error" is thrown.

Second, passing a date-type variable to match a worksheet date will fail
(not sure why), but passing the long integer equivalent works.