PDA

View Full Version : find closest date



av8tordude
09-28-2012, 01:53 PM
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
09-29-2012, 04:09 AM
check this kb article out:

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

snb
09-29-2012, 05:49 AM
or


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

av8tordude
10-04-2012, 12:39 AM
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
10-04-2012, 04:44 AM
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.