Find Next Date In Range From Input

    hi
    have a range of dates in a column
    i can match an input date with this code
    where the input date is in cell H20 (eg 04/04/2008)


    Code
    With ActiveSheet
    Range("D:D").Select
    Range("D:D").Find(What:=Range("H20"), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
 
End With


    the problem is that not all the dates in the range are consecutive and there may be the same date more than once eg
    01/04/2008
    03/04/2008
    03/04/2008
    05/04/2008


    the problem is if the date in cell H20 (inputted by user) does not exist in column D, it all goes wrong.


    what i would like to achieve is a way of incrementing the cell date by one day until it matches a date in the range, in this case if 02/04/2008 was entered 03/04/2008 would be picked up.


    any help and explaination to an answer greatly appreciated

    Re: Find Next Date In Range From Input


    try something like

    Code
    Sub datefind()
Range("A1").Value = Application.WorksheetFunction. _
VLookup(Range("H20"), Columns("D:D"), 1, True)
'using true will find the next nearest date without going past the date
End Sub

    Re: Find Next Date In Range From Input


    hi thanks but it didn't do what i was trying to achieve, perhaps i should have explained in more detail, if the date in the range did not exist, then i want it to find the next date, which would then activate that cell on a match.


    i did try to do a loop with cdate + 1, but failed, but that might convey better what i am trying to get.

    Re: Find Next Date In Range From Input


    That is what the VLOOKUP code Simon shows does. That is, TRUE as Range_lookup.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login