diesen Code für eine UserForm habe ich hier gefunden und auf meine Tabelle angepasst. Läuft hervorragend.
Allerdings ist mir dieser Teil After:=.Cells(13, 1) in der Zeile Set rngSearch = .Range("A13:F" & Lz).Find(Wha.... nicht klar.
Kann mir das jemand erklären.
Danke und Servus, Walter
Option Explicit
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdSearch_Click()
Dim objSH As Worksheet
Dim rngSearch As Range
Dim strFirst As String
Dim Lz As Long
If txtSearch "" Then
ListBox1.Clear
Set objSH = Sheets("Tabelle1")
With objSH
Lz = Application.Max(13, .Cells(Rows.Count, 2).End(xlUp).Row)
Set rngSearch = .Range("A13:F" & Lz).Find(What:=txtSearch, LookIn:=xlValues, LookAt:=xlPart, _
MatchCase:=False, After:=.Cells(13, 1))
If Not rngSearch Is Nothing Then
strFirst = rngSearch.Address
Do
If .Cells(rngSearch.Row, 3) = ComboBox1.Text Or ComboBox1.Text = "Alle" Then
ListBox1.AddItem .Cells(rngSearch.Row, 1)
ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(rngSearch.Row, 2)
ListBox1.List(ListBox1.ListCount - 1, 2) = .Cells(rngSearch.Row, 3)
ListBox1.List(ListBox1.ListCount - 1, 3) = .Cells(rngSearch.Row, 4)
ListBox1.List(ListBox1.ListCount - 1, 4) = .Cells(rngSearch.Row, 5)
ListBox1.List(ListBox1.ListCount - 1, 5) = .Cells(rngSearch.Row, 6)
ListBox1.List(ListBox1.ListCount - 1, 6) = .Cells(rngSearch.Row, 7)
End If
Set rngSearch = .Range("A13:F" & Lz).FindNext(rngSearch)
Loop While Not rngSearch Is Nothing And rngSearch.Address strFirst
End If
End With
If ListBox1.ListCount = 0 Then ListBox1.AddItem "Kein Treffer!"
End If
End Sub
Private Sub txtSearch_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then cmdSearch_Click
End Sub
Private Sub UserForm_Activate()
With ComboBox1
.List = Split("Alle") ';Horn;Trompete;Schlagzeug;Baß", ";")
.ListIndex = 0
End With