AW: ListBox - Werte anzeigen
14.04.2006 00:32:08
Pia
Hey Nepumuk,
habe es getestet, Die Werte stehen jetzt in Spalte G (also 7), habe versucht es anzupassen, aber es werden ALLE Werte angezeigt:
Dim vntArray As Variant
Dim strArray() As String, strTemp As String
Dim lngRow As Long, lngCounter As Long
With Datenbank
vntArray = .Range(.Cells(2, 7), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1)).Value
End With
Call sortieren(1, UBound(vntArray), vntArray)
For lngRow = 1 To UBound(vntArray)
If vntArray(lngRow, 7) strTemp Then
strTemp = vntArray(lngRow, 7)
lngCounter = lngCounter + 1
ReDim Preserve strArray(1 To lngCounter)
strArray(lngCounter) = strTemp
End If
Next
ComboBox1.List = strArray
Private Sub sortieren(lngLBound As Long, lngUBound As Long, vntArray As Variant)
Dim lngIndex1 As Long, lngIndex2 As Long
Dim vntBuffer As Variant, vntTemp As Variant
lngIndex1 = lngLBound
lngIndex2 = lngUBound
vntTemp = vntArray((lngLBound + lngUBound) \ 2, 1)
Do
Do While vntArray(lngIndex1, 7) < vntTemp
lngIndex1 = lngIndex1 + 1
Loop
Do While vntTemp < vntArray(lngIndex2, 1)
lngIndex2 = lngIndex2 - 1
Loop
If lngIndex1 <= lngIndex2 Then
vntBuffer = vntArray(lngIndex1, 7)
vntArray(lngIndex1, 7) = vntArray(lngIndex2, 7)
vntArray(lngIndex2, 7) = vntBuffer
lngIndex1 = lngIndex1 + 1
lngIndex2 = lngIndex2 - 1
End If
Loop Until lngIndex1 > lngIndex2
If lngLBound < lngIndex2 Then Call sortieren(lngLBound, lngIndex2, vntArray)
If lngIndex1 < lngUBound Then Call sortieren(lngIndex1, lngUBound, vntArray)
End Sub