Find... >= tDate

[PcMax]

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

Thanks in advance

[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

[snb]

Sub M_snb()
    [C7:C25000] = [if(B7:B25000>=A4,B7:B25000,"")]
End Sub

[Rick Rothstein]

Sub M_snb()
    [C7:C25000] = [if(B7:B25000>=A4,B7:B25000,"")]
End Sub

I really hate the square bracket notation. Anyway, that aside, your code returns a range of values to the worksheet which is different than what the OP's code suggested he wanted (you may be right in that being his ultimate goal, but he didn't say that and so I just wanted to point out that difference for his benefit).

[Rick Rothstein]

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

snb's post reminded me that I used more code than was needed in the above macro as this shorter code would work the same (although it does not report "All OK" if all the dates are less than A4's date; rather, it does nothing which matches what PcMax's code does in that circumstance)...

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

And while I hate the square bracket notation snb used in his post, the above would be shortened even more by using it (assuming you do not mind that notation)...

Sub DateTooLate()
  MsgBox [MIN(IF(B7:B2500>=A4,ROW(B7:B2500),""))]
End Sub

[PcMax]

snb's post reminded me that I used more code than was needed in the above macro as this shorter code would work the same (although it does not report "All OK" if all the dates are less than A4's date; rather, it does nothing which matches what PcMax's code does in that circumstance)...

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

And while I hate the square bracket notation snb used in his post, the above would be shortened even more by using it (assuming you do not mind that notation)...

Sub DateTooLate()
  MsgBox [MIN(IF(B7:B2500>=A4,ROW(B7:B2500),""))]
End Sub

I have tested all your code, thank you for your suggestions.
The code I must return a single value as indicated by Rick at the beginning of the range of my research.
To complete my selection I just have to find a different formula with the value: B7:B2500<=B4 (B4 value > A4)

[Rick Rothstein]

Are you looking for something like this?

Sub DateJustRight()
  MsgBox Evaluate("MAX(IF((B7:B2500>=A4)*(B7:B2500<=B4),ROW(B7:B2500),))")
End Sub

[PcMax]

Are you looking for something like this?

Sub DateJustRight()
  MsgBox Evaluate("MAX(IF((B7:B2500>=A4)*(B7:B2500<=B4),ROW(B7:B2500),))")
End Sub

Thank you very much for the control of dates

[snb]

Do not consider my suggestions as solutions but as suggestions for a method to attain a certain goal.
You are free to adapt the suggestions to your wishes/requirements.

you could simply use this formula:

=MATCH(A4,B7:B25000,1) 

[PcMax]

Do not consider my suggestions as solutions but as suggestions for a method to attain a certain goal.
You are free to adapt the suggestions to your wishes/requirements.

you could simply use this formula:

=MATCH(A4,B7:B25000,1) 

Snb thank you anyway for the suggestions, I had already discarded the use of the MATCH formula

1) Does not find the required value: >=, but choose the previous.
2) In the case in which the list having two or more dates homogeneous would identify the position of the last value

I thank all of the suggestions received