VBA:找到具有确切日期或更高日期的第一行

Question

下面的代码向我展示了2019年7月5日出现在A列A中的第一行。但是，有时此确切日期不在列中，我想获得7月5日之后的日期第一行。我怎么做？

Sub Test()
    Debug.Print ThisWorkbook.Sheets(1).Range("A:A").Find(CDate("05.07.19")).Row
End Sub

Answer 1

我敢打赌，这是一个更漂亮的方法，但这是一种

Sub Test()
Dim Search As Range, myrng As Range
Dim rslt As Long, FRow As Long
Set myrng = ThisWorkbook.Sheets(1).Range("A:A")
Set Search = myrng.Find(CDate("05 July 19"))
If Not Search Is Nothing Then
    rslt = Search.Row 
Else
    Set Search = myrng.Find(Mid(CDate("July 2019"), 3, 6)
    If Not Search Is Nothing Then
        FRow = Search.Row
        Do Until Search Is Nothing
            If Day(Search) > 5 Then
                rslt = Search.Row
                Exit Do
            End If
            Set Search = myrng.FindNext(Search)
            If Not Search Is Nothing Then
                If Search.Row = FRow Then Exit Do
            End If
        Loop
    End If
End If
Debug.Print rslt

正如原始人正确指出的那样，日期格式可能会带来问题。

Answer 2

尝试此udf：

Public Function GET_ROW_OF_DATE(ByVal vThisDate As Date, ByVal vOnThisRange As Range)
Dim rng As Range
With Application.WorksheetFunction
    If .CountIf(vOnThisRange, vThisDate) = 0 Then
        'if there is no exact match, count if any is higher
        If .CountIf(vOnThisRange, ">" & CDbl(vThisDate)) = 0 Then
            'There is no match
            GET_ROW_OF_DATE = 0
            Exit Function
        Else
            For Each rng In vOnThisRange
                If rng.Value > vThisDate Then
                    GET_ROW_OF_DATE = rng.Row
                    Exit Function
                End If
            Next rng
        End If
    Else
        'there is a match = to vThisDate
        For Each rng In vOnThisRange
            If rng.Value = vThisDate Then
                GET_ROW_OF_DATE = rng.Row
                Exit Function
            End If
        Next rng
    End If
End With
End Function

它没有与输入相等或更高的日期，然后它将返回0。希望您可以适应您的需求。

Answer 3

也许可以尝试一下：

编辑

Sub tryme()
   Dim rng As Range
Set rng = ThisWorkbook.Sheets(1).Range("A:A")
For Each c In rng.Cells
  If IsDate(c.Value) And c.Value = DateValue(Format("05/07/19", "dd/mm/yyyy")) Then
    firstValue = c.Value
    firstAddress = c.Address
    GoTo ENDSEARCH
  End If
Next
For Each c In rng.Cells
  If IsDate(c.Value) And c.Value > DateValue(Format("05/07/19", "dd/mm/yyyy")) Then
    firstValue = c.Value
    firstAddress = c.Address
    GoTo ENDSEARCH
  End If
Next

ENDSEARCH:
MsgBox "The first value greater than 05/07/2019 is in cell " & firstAddress & _
       "; - it has value " & firstValue

End Sub