Option Explicit
Public Sub Test_Aufruf()
Dim rngFallNr As Excel.Range
Dim strFallNr As String
strFallNr = "00002"
If FindeFallNr(strFallNr, rngFallNr) Then
MsgBox "Fallnummer '" & strFallNr & "' wurde gefunden in Zelle: " & rngFallNr.Address, vbInformation
Else
MsgBox "'" & strFallNr & "' wurde nicht gefunden.", vbExclamation
End If
End Sub
Public Function FindeFallNr(Nummer As String, Optional ByRef FallNr As Excel.Range) As Boolean
Dim rngFallNr As Excel.Range
Dim rngFallNrBereich As Excel.Range
With Worksheets("Tabelle1").Rows(1)
Set rngFallNr = .Find("Fallnummer", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False)
End With
If Not rngFallNr Is Nothing Then
With rngFallNr.Worksheet
Set rngFallNrBereich = .Range(rngFallNr.Offset(1), .Cells(.Rows.Count, rngFallNr.Column).End(xlUp))
End With
If rngFallNrBereich.Row > rngFallNr.Row Then
Set rngFallNr = rngFallNrBereich.Find(Nummer, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)
If Not rngFallNr Is Nothing Then
Set FallNr = rngFallNr
FindeFallNr = True
End If
End If
End If
End Function