Find... >= tDate

[Rick Rothstein]

Hi,

I have a list of dates in ascending order...

03/01/2001
08/01/2001
10/01/2001
13/01/2001
17/01/2001
20/01/2001

Sub Test()
    If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
    tDate = CDbl(DateValue([A4].Value)) '<--- Selection.NumberFormat = "m/d/yyyy"
    For Lval = 7 To 25000
        If CDbl(Cells(Lval, 2)) >= tDate Then
            MsgBox Lval
            Exit Sub
        End If
    Next
End Sub

Using this code that displays the correct data, it is possible a different solution

It does not actually display the "correct data", rather, it display the first row number of a date in B7:B2500 that is greater than or equal to the date in A4, if there is one. Here is a shorter bit of code (actually, a one-liner) that should do the same thing...

Sub DateTooLate()
  MsgBox Replace(Evaluate("MIN(IF(B7:B2500>=A4,ROW(B7:B2500),99999))"), 99999, "All OK")
End Sub