Find a date in list less or equal than target date

  • Thread starter Thread starter u473
  • Start date Start date
U

u473

Sorted, non contiguous dates are in column "A" formatted as MM/DD/
YY.
Target date is retrieved from an Inputbox. Format MM/DD/YYYY
However. Because the stored value is not the same as the displayed
Formatted date, I have tried. to use DateValue inside a Vlookup or
Find What, and test for Less or Equal inside a Loop, since my range
of dates may not be contiguous.
So far I have not found the solution.
Your help will be appreciated.
J.P.
 
Do you mean by non contiguous that there are empty cells in among your
dates in column A, or that some dates are missing within a contiguous
range?

Pete
 
Thank you for your question.
The date column, from the header row down to the last populated row
will not have empty cells, and could be displayed as follows :
.. A B C
1. Dates
2. 10/06/10 ... ...
3. 10/07/10 ...
4. 10/09/10
5. 10/10/10
6. 10/11/10
7. 10/15/10
8. 10/16/10
Dates will appear to be random, but sorted.
Once the Date Less or Equal to the Target is found
I would use an Offset to retrieve the corresponding data in Col. "B".
 
Do you mean by non contiguous that there are empty cells in among your
dates in column A, or that some dates are missing within a contiguous
range?

Pete



- Show quoted text -
Click to expand...

Just use the vlookup formual or this macro
Option Explicit
Sub VlookupDateSAS()
Dim myDate As Date
Dim foundvalue As Variant
myDate = DateSerial(2010, 10, 4)
foundvalue = Application.VLookup(CLng(myDate), Range("a2:c22"), 2)
MsgBox foundvalue
End Sub
 
Thank you both.
With your first code I was not able to fix the Mismatch Error
With the second code it worked and I got a piece of education at the
same time
with the double step, VLookup to find the Less or Equal, then
FindWhat.
You made my day,
Thanks again,
J.P.
 
Back
Top