Option
Explicit
Public
Sub
MySearch4Title()
Dim
Maschine, ETnummer, TitelNummer
Dim
result
With
Worksheets(
"Tabelle1"
)
With
.Range(
"B1"
, .Cells(.Rows.Count,
"B"
).
End
(xlUp)).Resize(, 1)
.Insert xlShiftToRight
With
.Offset(, -1)
.FormulaR1C1 =
"=CONCATENATE(RC[1],CHAR(10),RC[2])"
Maschine =
"KM1"
ETnummer =
"33456"
TitelNummer =
"407"
Set
result = .Find(Maschine & vbLf & ETnummer, , xlValues, xlWhole, xlByRows, xlNext,
False
)
If
Not
result
Is
Nothing
Then
Set
result = .Find(Maschine & vbLf & TitelNummer &
"-*"
, result, xlValues, xlWhole, xlByRows, xlPrevious,
False
)
If
Not
result
Is
Nothing
Then
result = Split(result, vbLf)
result = Right$(result(1), Len(result(1)) - Len(TitelNummer) - 1)
Else
result = Empty
End
If
Else
result = Empty
End
If
If
Not
IsEmpty(result)
Then
Debug.Print Maschine; Tab(8); ETnummer; Tab(18); TitelNummer; Tab(10);
"=> "
; result
Else
Debug.Print Maschine; Tab(8); ETnummer; Tab(18); TitelNummer; Tab(10);
"?? nicht_gefunden ??"
End
If
.Delete xlShiftToLeft
End
With
End
With
End
With
End
Sub