Find Date an return row number

[Lisa_S]

Hello,

in the attachement you can find my excel sheet. I hope this helps.

I need a VBA code to look for date A (German format) and return the row number (9) as a variable X to further use it in a VBA script.

Additionally I want to look for date B (which can`t be found) so I want the script to return the row number of the next available date after date B which is in this case 10 and return this to variable Y.

I`ve tried a lot but couldn`t get the code done.

I hope you can help me

Best
Lisa

[jonh]

Hi.

Sub test()
    Debug.Print FindDt(Range("C2"), Range("B7:B20"))
    Debug.Print FindDt(Range("C3"), Range("B7:B20"))
End Sub

Function FindDt(f As Range, r As Range) As Long
On Error Resume Next

    FindDt = r.Find(f.Value).Row
    
    If FindDt = 0 Then
        Dim ary(), i
        ary = r
        For i = LBound(ary) To UBound(ary)
            If ary(i, 1) >= f.Value Then
                FindDt = r.Row + i - 1
                Exit For
            End If
        Next
    End If
End Function

[Yongle]

Welcome to the forum Lisa_S
Another option for you

This checks for a match to the value in cell C2
If exact match then X contains row number
If nearest match the Y contains row number
If date input exceeds all datum values, it exits the sub.
(To apply to C3 use the same code replacing C2 with C3)

Message boxes included for information (can delete them if you want)

Sub FindDateRow()
'declare variables, set ranges and known values
Dim A, B, X, Y, MyRange, c
    With ActiveSheet
        A = .Range("C2").Value
        B = A   'need to remember original value
        Set MyRange = .Range("B7:B20")
    End With
'check to ensure that date input in C2 not too great
If A > WorksheetFunction.Max(MyRange) Then
    MsgBox "Input value exceeds all datum values"
    Exit Sub
End If
'search for match or next highest
X = 0
Do Until X <> 0
    For Each c In MyRange
        If c = A Then
            X = c.Row
        End If
            If X = 0 And c.Row = MyRange.End(xlDown).Row Then
                A = A + 1
            End If
    Next c
Loop
    If B = A Then
    MsgBox "Exact Match in row " & X
    Else
        Y = X
        X = 0
        MsgBox "Nearest Match in row " & Y
    End If
End Sub

[Lisa_S]

Hey,
thank you both very much for your fast help! You`re the best!