AW: entschuldigung - gemeint habe ich aufsteigend
15.01.2019 14:52:03
UweD
Hallo
so?
Modul2
Option Explicit
Sub Anzahl()
Dim Arr, ArrN, z1 As Integer, Sp As Integer, LR As Long
Dim i As Long, Kompl As String, Anz As Integer, Eintr, Neutxt As String
z1 = 2 'erste Zeile mit Daten
Sp = 7 'Spalte G
With Sheets("Tabelle2")
LR = .Cells(.Rows.Count, Sp).End(xlUp).Row 'letzte Zeile der Spalte
For i = z1 To LR
Kompl = .Cells(i, Sp)
If Kompl <> "" Then
Arr = Split(Zell_sort(Kompl, ","), ",")
Neutxt = ""
For Each Eintr In Arr
If InStr(Neutxt, Eintr & "(") = 0 Then
Anz = Len(Kompl) - Len(Replace(Kompl, Eintr, ""))
Neutxt = Neutxt & Eintr & "(" & Anz & ")" & ", "
End If
Next
Neutxt = Left(Neutxt, Len(Neutxt) - 2) 'letzte Komma weg
.Cells(i, Sp + 1) = Neutxt
End If
Next
End With
End Sub
'Unterprogramm zum Sortieren
Function Zell_sort(zahlenstring As String, Optional trenner As String) As String
Dim Count As Integer
Dim Count2 As Integer
Dim Temp As String
Dim ainput
If IsMissing(trenner) Then
trenner = " "
End If
ainput = Split(zahlenstring, trenner)
If Ubound(ainput) = 0 Then
Zell_sort = "#?trenner?#"
Exit Function
End If
For Count = 0 To Ubound(ainput)
For Count2 = Count To Ubound(ainput)
If ainput(Count) > ainput(Count2) Then
Temp = ainput(Count)
ainput(Count) = ainput(Count2)
ainput(Count2) = Temp
End If
Next Count2
Next Count
Zell_sort = Join(ainput, trenner)
End Function
LG UweD