Find a date in list less or equal than target 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.
 
Ad

Advertisements

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
 
U

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

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
 
Ad

Advertisements

U

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top