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)

    With ActiveSheet
        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

    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

    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!