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

 

[Ron Rosenfeld]

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.

Without your code, try this for example of how to accomplish your
task:

====================================
Option Explicit
Sub foo()
Dim dTarget As Date
Dim DateRange As Range
Dim rTarget As Range
Set DateRange = Range("A1:a100") 'better ways to set this up
dTarget = InputBox("Date: ")

dTarget = WorksheetFunction.VLookup(CLng(dTarget), DateRange, 1)
Set rTarget = DateRange.Find(what:=dTarget)

MsgBox (rTarget(1, 2).Address & ":" & vbTab & rTarget(1, 2).Value)

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.

 

[Ron Rosenfeld]

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.

Glad to help. Thanks for the feedback.