AW: Werte in einer einzelnen Zelle sortieren
19.06.2012 12:56:03
Reinhard
Hallo Marc,
genau das sollen die zwei Codezeilen tuen.
Du sprichst von einer Zelle, die erste MsgBox zeigt dir die Zelladressen deiner einen Zelle.
Die zweite MB die Anzahl der Zellen.
Probiere mal den nachfolgenden Code.
Gruß
Reinhard
Sub tt()
Dim wks As Worksheet, Spa As Long, Zei As Long
Set wks = Worksheets("Tabelle2")
Worksheets.Add after:=Worksheets(Worksheets.Count)
With ActiveSheet
wks.Range("H115").Copy .Range("A1")
.Range("A1").Value = .Range("A1").Value & ","
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
Spa = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(1, 1), .Cells(1, Spa)).Copy
.Range("A2").PasteSpecial Paste:=xlAll, Transpose:=True
.Rows(1).Delete
.Range(Cells(1, 1), Cells(Spa, 1)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
wks.Range("H115").ClearContents
For Zei = 1 To Spa - 1
wks.Range("H115").Value = wks.Range("H115").Value & .Range("A" & Zei).Value & " "
Next Zei
wks.Range("H115").Value = wks.Range("H115").Value & Replace(.Range("A" & Zei).Value, _
",", "")
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
wks.Activate
End With
End Sub