Hallo zusammen
Habe folgenden Code gefunden und auf meine Bedürfnisse angepasst.
Die Suche der Zeile "test" sollte aber ohne Box-Suche starten. Könnte Ihr mir evtl. helfen, diese Funktion umzuschreiben, damit auto. die Zeile "test" gesucht wird?
Ich wäre euch sehr dankbar.
Lg Ralf
Anbei der Code:
Private Sub CommandButton3_Click()
Dim oRange As Range
Dim aCell As Range
Dim bCell As Range
Dim ws As Worksheet
Dim Gefunden As String
Dim Zeile As Long
Dim SN As String
SN = Application.InputBox("Suchbegriff")
On Error GoTo Fehler
Set ws = Worksheets("Verrechnung")
Set oRange = ws.Columns(1)
Set aCell = oRange.Find(What:=SN, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
Set bCell = aCell
Gefunden = aCell.Row
Do
Set aCell = oRange.FindNext(After:=aCell)
If Not aCell Is Nothing Then
If aCell.Address = bCell.Address Then Exit Do
Gefunden = Gefunden & ", " & aCell.Row
Else
Exit Do
End If
Loop
Else
MsgBox SN & " nicht gefunden"
Exit Sub
End If
Zeile = Right(Gefunden, 3)
Rows(Zeile - 1).Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A" & Zeile).Copy
Range("A" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("B" & Zeile).Copy
Range("B" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("E" & Zeile).Copy
Range("E" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("F" & Zeile).Copy
Range("F" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("G" & Zeile).Copy
Range("G" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("H" & Zeile).Copy
Range("H" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("I" & Zeile).Copy
Range("I" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("J" & Zeile).Copy
Range("J" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("K" & Zeile).Copy
Range("K" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("T" & Zeile).Copy
Range("T" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("U" & Zeile).Copy
Range("U" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("V" & Zeile).Copy
Range("V" & Zeile - 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Exit Sub