Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date VBA Lookup
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date VBA Lookup
jlclyde wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date LookUp | Excel Worksheet Functions | |||
Date Lookup | Excel Discussion (Misc queries) | |||
Lookup MIN Date | Excel Worksheet Functions | |||
Lookup MIN Date | Excel Worksheet Functions | |||
Lookup MIN Date | Excel Worksheet Functions |