Option Explicit
Dim arrList()
Private Sub ListboxLaden()
Dim arrTab(): arrTab = Fahrzeugbestand.ListObjects(1).DataBodyRange.Value
arrList = Application.Index(arrTab, Evaluate("row(1:" & UBound(arrTab, 1) & ")"), Array(1, 2, 3, 5, 7))
With ListBoxCar
.ColumnCount = UBound(arrList, 2)
.ColumnWidths = Join(Array(80, 70, 80, 90, 150), ";")
.List = arrList
End With
End Sub
Private Sub Suchen()
Dim i&, j&, k&, tmp()
For i = 1 To UBound(arrList)
If (Marke = "" Or InStr(1, arrList(i, 1), Marke, vbTextCompare) > 0) _
And (Model = "" Or InStr(1, arrList(i, 2), Model, vbTextCompare) > 0) _
And (Erstzulassung = "" Or InStr(1, arrList(i, 3), Erstzulassung, vbTextCompare) > 0) _
And (Getriebe = "" Or InStr(1, arrList(i, 4), Getriebe, vbTextCompare) > 0) _
And (Kraftstoff = "" Or InStr(1, arrList(i, 5), Kraftstoff, vbTextCompare) > 0) _
Then
k = k + 1
End If
Next i
If k = 0 Then
ListBoxCar.Clear: Exit Sub
End If
ReDim tmp(1 To k, 1 To UBound(arrList, 2))
k = 0
For i = 1 To UBound(arrList)
If (Marke = "" Or InStr(1, arrList(i, 1), Marke, vbTextCompare) > 0) _
And (Model = "" Or InStr(1, arrList(i, 2), Model, vbTextCompare) > 0) _
And (Erstzulassung = "" Or InStr(1, arrList(i, 3), Erstzulassung, vbTextCompare) > 0) _
And (Getriebe = "" Or InStr(1, arrList(i, 4), Getriebe, vbTextCompare) > 0) _
And (Kraftstoff = "" Or InStr(1, arrList(i, 5), Kraftstoff, vbTextCompare) > 0) _
Then
k = k + 1
For j = 1 To UBound(tmp, 2)
tmp(k, j) = arrList(i, j)
Next j
End If
Next i
ListBoxCar.List = tmp
End Sub
Private Sub Erstzulassung_Change()
Suchen
End Sub
Private Sub Getriebe_Change()
Suchen
End Sub
Private Sub Kraftstoff_Change()
Suchen
End Sub
Private Sub Marke_Change()
Suchen
End Sub
Private Sub Model_Change()
Suchen
End Sub
Private Sub UserForm_Initialize()
ListboxLaden
End Sub