So habe ich es gelöst
08.07.2018 12:37:36
Sabrina
Ich habe eine weitere Userform "Seriennummer_suchen" erstellt.
Es geht mit Sicherheit eleganter. Aber es funktioniert :)
Private Sub UserForm_Activate()
Dim c, rng, var As Range
Dim sFirst, tFirst, Name As String
Dim i, Max, KdNr, Zahl As Integer
Dim Datum As Date
Dim GNR, geraet, Model, Hersteller, Typ, Auftrag As String
GNR = Range("Geraetenr")
ListBox2.Clear
ListBox1.AddItem
ListBox1.List(i, 0) = "GeräteNr"
ListBox1.List(i, 1) = "| Art"
ListBox1.List(i, 2) = "| Hersteller"
ListBox1.List(i, 3) = "| Model"
ListBox1.List(i, 4) = "| Seriennummer"
Set rng = Workbooks("Geraete.xls").Worksheets("geraete").Range("F:F") _
.Find(What:=GNR, LookIn:=xlValues, LookAt:=xlPart, After:=Range("F65536"))
If Not rng Is Nothing Then
sFirst = rng.Address
i = 0
ListBox2.AddItem
ListBox2.List(i, 0) = rng.Offset(0, -5)
ListBox2.List(i, 1) = "|"
ListBox2.List(i, 2) = rng.Offset(0, -4)
ListBox2.List(i, 3) = "| "
ListBox2.List(i, 4) = rng.Offset(0, -3)
ListBox2.List(i, 5) = "| "
ListBox2.List(i, 6) = rng.Offset(0, -2) & " " & rng.Offset(0, -1)
ListBox2.List(i, 7) = "| "
ListBox2.List(i, 8) = rng.Offset
i = i + 1
Do
Set rng = Workbooks("Geraete.xls").Worksheets("geraete").Range("F:F").FindNext(After:= _
rng)
If rng.Address = sFirst Then Exit Do
ListBox2.AddItem
ListBox2.List(i, 0) = rng.Offset(0, -5)
ListBox2.List(i, 1) = "|"
ListBox2.List(i, 2) = rng.Offset(0, -4)
ListBox2.List(i, 3) = "| "
ListBox2.List(i, 4) = rng.Offset(0, -3)
ListBox2.List(i, 5) = "| "
ListBox2.List(i, 6) = rng.Offset(0, -2) & " " & rng.Offset(0, -1)
ListBox2.List(i, 7) = "| "
ListBox2.List(i, 8) = rng
i = i + 1
Loop
Zahl = ListBox2.ListCount
End If
If ListBox2.ListCount = 0 Then
Unload Me
Exit Sub
End If
If ListBox2.ListCount = 1 Then
Seriennummer.Show
Unload Me
Exit Sub
End If
End Sub