find closest date

[av8tordude]

The code below finds the exact date in A5, but if no date is listed, I would like to find the closet date that is in A5. Can someone assist. Thanks

Dim My_Row As Long

On Error Resume Next
My_Row = 0
My_Row = Application.WorksheetFunction.Match(Range("A5"), Range("A9:A10009"), 0)
On Error GoTo 0

If My_Row <> 0 Then
My_Row = My_Row + 8
Application.EnableEvents = False
Range("A" & My_Row).Activate
Application.EnableEvents = True
End If

[mancubus]

check this kb article out:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1056

[snb]

or


Sub snb()
Cells([MATCH(SMALL(ABS((A5-(A9:A10009))),1),ABS((A5-(A9:A10009))),0)]+8, 1).Select
End Sub

[av8tordude]

check this kb article out:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1056
In this code, the date is highlighted, but if the date is off the screen, how can I put the date in view? Basically activate the cell.

Thanks

[mancubus]

In this code, the date is highlighted, but if the date is off the screen, how can I put the date in view? Basically activate the cell.

Thanks

snb's code simply does that.
you can remove "+8" (which is added because of My_Row = My_Row + 8 line in your code) from row reference to activate the found cell.