ich lasse eine Listbox über den Wert mit den Daten aus 3 Dateien füllen
Das funktioniert wunderbar.
Nun meine Frage, kann man die Liste nach dem Datum absteigend sortieren lassen?
LG
Sabbel
Private Sub FillList()
Dim arch, rng, var As Range
Dim Zahl, wert As Long
Dim tFirst, sFirst, GeNr As String
Dim Max, i As Integer
ListBox2.Clear
ListBox1.AddItem
ListBox1.List(i, 0) = "Hersteller"
ListBox1.List(i, 1) = "| Typ"
ListBox1.List(i, 2) = "| Seriennummer"
ListBox1.List(i, 3) = "| Datum"
ListBox1.List(i, 4) = "| Kosten"
ListBox1.List(i, 5) = "| Auftragsnummer"
' Auftrag
Set rng = Workbooks("Auftrag.xls").Worksheets("Daten").Range("C:C") _
.Find(What:=txtSearch, LookIn:=xlValues, LookAt:=xlPart, After:=Range("C15000"))
If Not rng Is Nothing Then
sFirst = rng.Address
ListBox2.AddItem
ListBox2.List(i, 0) = rng.Offset(0, 1)
ListBox2.List(i, 3) = "| " & rng.Offset(0, 3)
ListBox2.List(i, 4) = "| " & rng.Offset(0, 108)
ListBox2.List(i, 5) = "| "
ListBox2.List(i, 6) = rng.Offset(0, -2)
i = i + 1
Do
Set rng = Workbooks("Auftrag.xls").Worksheets("Daten").Range("C:C").FindNext(After:= _
_
rng)
If rng.Address = sFirst Then Exit Do
ListBox2.AddItem
ListBox2.List(i, 0) = rng.Offset(0, 1)
ListBox2.List(i, 3) = "| " & rng.Offset(0, 3)
ListBox2.List(i, 4) = "| " & rng.Offset(0, 108)
ListBox2.List(i, 5) = "| "
ListBox2.List(i, 6) = rng.Offset(0, -2)
i = i + 1
Loop
End If
' Archiv
Set rng = Workbooks("Archiv.xls").Worksheets("Daten").Range("C:C") _
.Find(What:=txtSearch, LookIn:=xlValues, LookAt:=xlPart, After:=Range("C15000"))
If Not rng Is Nothing Then
sFirst = rng.Address
ListBox2.AddItem
ListBox2.List(i, 0) = rng.Offset(0, 1)
ListBox2.List(i, 3) = "| " & rng.Offset(0, 3)
ListBox2.List(i, 4) = "| " & rng.Offset(0, 108)
ListBox2.List(i, 5) = "| "
ListBox2.List(i, 6) = rng.Offset(0, -2)
i = i + 1
Do
Set rng = Workbooks("Archiv.xls").Worksheets("Daten").Range("C:C").FindNext(After:=rng)
If rng.Address = sFirst Then Exit Do
ListBox2.AddItem
ListBox2.List(i, 0) = rng.Offset(0, 1)
ListBox2.List(i, 3) = "| " & rng.Offset(0, 3)
ListBox2.List(i, 4) = "| " & rng.Offset(0, 108)
ListBox2.List(i, 5) = "| "
ListBox2.List(i, 6) = rng.Offset(0, -2)
i = i + 1
Loop
' Die Datenbank Geräte aufrufen
GeNr = ListBox2.List(0, 0)
Set var = Workbooks("Geraete.xls").Worksheets("geraete").Range("A:A") _
.Find(What:=GeNr, LookIn:=xlValues, LookAt:=xlPart, After:=Range("A8000"))
If Not var Is Nothing Then
tFirst = var.Address
ListBox2.List(0, 0) = " " & var.Offset(0, 2)
ListBox2.List(0, 1) = "| " & var.Offset(0, 4)
ListBox2.List(0, 2) = "| " & var.Offset(0, 5)
Max = ListBox2.ListCount
wert = Max - 1
For i = 1 To wert
Zahl = ListBox2.List(i, 0)
Set var = Workbooks("Geraete.xls").Worksheets("geraete").Range("A:A") _
.Find(What:=Zahl, LookIn:=xlValues, LookAt:=xlPart, After:=Range("A8000"))
tFirst = var.Address
ListBox2.List(i, 0) = " " & var.Offset(0, 2)
ListBox2.List(i, 1) = "| " & var.Offset(0, 4)
ListBox2.List(i, 2) = "| " & var.Offset(0, 5)
Next i
End If
End If
If ListBox2.ListCount = 0 Then
Unload Me
Exit Sub
End If
End Sub