Find a date in list less or equal than target date
 

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.

Find a date in list less or equal than target date
 

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

On Oct 6, 8:18*am, u473 wrote:
*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.

Find a date in list less or equal than target date
 

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

Find a date in list less or equal than target date
 

On Oct 6, 4:01*am, Pete_UK wrote:
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

On Oct 6, 8:18*am, u473 wrote:



*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.- Hide quoted text -

- 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

Find a date in list less or equal than target date
 

On Wed, 6 Oct 2010 00:18:18 -0700 (PDT), u473 wrote:

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
==============================

Find a date in list less or equal than target date
 

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.

Find a date in list less or equal than target date
 

On Wed, 6 Oct 2010 09:34:36 -0700 (PDT), u473 wrote:

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.