Find a date in list less or equal than target date

[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.

 

[Pete_UK]

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

 

[u473]

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".

 

[Don Guillett Excel MVP]

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 -

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

 

[u473]

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.