Text aus einer Spalte in eine Zelle durch Trennzeichen kopieren

[Lars1904]

Hallo zusammen,

ich bin absoluter Anfänger auf dem Bereich VBA-Programmierung, benötigte aber kurz Hilfe von jemanden, da ich voll auf dem Schlauch stehe.


Ich möchte den Inhalt von S4:S5000 als Text, getrennt durch ", " in die Zelle R2 kopieren.

Leere Zeilen sollen ignoriert werden.

Wie müsste der VBA Code hierfür lauten?

Vielen lieben Dank im Vorfeld!

Viele Grüße
Lars

[LCohen]

Schau Dir Split und Join an.

[Lars1904]

Vielen Dank für deine Antwort!

Leider komme ich damit auch noch nicht so recht weiter. Kannst du mir einen Start-Tipp für den Code geben?

Danke!

[Werner.M]

Hallo,

was steht denn in den Zellen von 4-5000?
Du solltest dir im Klaren darüber sein, dass die Anzahl der Zeichen in einer Zelle beschränkt ist.
Bei mir ist bei 31794 Zeichen in der Zelle Schluß.

Gruß Werner

[schauan]

Hallo Lars,

Du wirst Schwierigkeiten bekommen, den Text von gut 5000 Zeilen in eine Zelle zu bekommen. Ansonsten könnte das so funktionieren - Spalten und Zellen musst Du noch anpassen:

Code:

Option Explicit

Sub test()
'Variablendeklarationen
Dim lLastRow%, arrDaten
'Entfernen der Leerzeilen mittels sortieren
'Und Daten in einer Zelle zusammenfassen
'Mit dem Blatt Tabelle2
With Sheets("Tabelle2")
    'In B4 und B5 Startwerte fuer Reihenfolge eintragen
    .Range("B4") = 1
    .Range("B5") = 2
    'Autoausfuellen fuer Reihenfolge der Eintraege
    .Range("B4:B5").AutoFill Destination:=.Range("B4:B10")
    'eventuelle alte Sortiereinstellung entfernen
    .Sort.SortFields.Clear
    'Sortierung definieren - nach Spalte A
    .Sort.SortFields.Add2 Key:=.Range( _
        "A4:A10"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    'Mit der Sortierung A anfangen
    With .Sort
        'Bereich und Einstellungen festlegen
        .SetRange Sheets("Tabelle2").Range("A4:B10")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        'Ausfuehren
        .Apply
    'Ende Mit der Sortierung A anfangen
    End With
    'letzte belegte zeile in Spalte A ermitteln
    lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    'eventuelle alte Sortiereinstellung entfernen
    .Sort.SortFields.Clear
    'Sortierung definieren - nach Spalte B
    .Sort.SortFields.Add2 Key:=.Range( _
        "B4:B" & lLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    'Mit der Sortierung B anfangen
    With .Sort
        'Bereich und Einstellungen festlegen
        .SetRange Sheets("Tabelle2").Range("A4:B" & lLastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        'Ausfuehren
        .Apply
    'Ende Mit der Sortierung B anfangen
    End With
'Mit dem Blatt Tabelle2
End With
'Daten in Array aufnehmen
arrDaten = WorksheetFunction.Transpose(Range("A4:A" & lLastRow).Value)
'Array zu einer Zeichenkette zusammenfassen
arrDaten = Join(arrDaten, ",")
'Zeichenkette in A1 eintragen
Range("A1") = arrDaten
End Sub

[Lars1904]

Von den 5000 Zeilen sind in der Regel höchstens 50 befüllt (jeweils Zahlen mit 5 Stellen), von daher sollte der Platz kein Problem sein.

Danke!

---

@schauan

Vielen Dank für den Code! Sorry, ich bin echt absoluter Anfänger. Tut mir leid für die blöde Nachfrage, aber welche Spalten und Zellen muss ich genau anpassen?

Und vielleicht für mich zum Verständnis: Wofür sind die Sortierungs-Befehle in dem Code?


Tausend Dank!

[Werner.M]

Hallo,

den Blattnamen mußt du anpassen.

Code:

Public Sub aaa()
Dim i As Long, strWert As String

With Worksheets("Tabelle3")
    For i = 4 To 5000
        If .Cells(i, "S") <> "" Then
            If strWert = vbNullString Then
                strWert = .Cells(i, "S")
            Else
                strWert = strWert & "," & .Cells(i, "S")
            End If
        End If
    Next i
    If Not strWert = vbNullString Then
        .Range("R2") = strWert
    End If
End With
End Sub

[Lars1904]

Überragend!!!!! Es klappt! Danke!!!!

Ich habe allerdings noch eine Rückfrage:

ich habe den Code leicht angepasst:

Public Sub aaa()
Dim i As Long, strWert As String
With Worksheets("WEPA")
    For i = 5 To 5000
        If .Cells(i, "Q") <> "" Then
            If strWert = vbNullString Then
                strWert = .Cells(i, "Q")
            Else
                strWert = strWert & ", " & .Cells(i, "Q")
            End If
        End If
    Next i
    If Not strWert = vbNullString Then
        .Range("G3") = strWert
    End If
   
    For i = 5 To 5000
        If .Cells(i, "T") <> "" Then
            If strWert = vbNullString Then
                strWert = .Cells(i, "T")
            Else
                strWert = strWert & ", " & .Cells(i, "T")
            End If
        End If
    Next i
    If Not strWert = vbNullString Then
        .Range("G12") = strWert
    End If
   
End With
End Sub




Wieso werden in G12 nun die Inhalte aus Spalte Q und T angegeben?!

Was muss ich ändern, dass in G3 nur Inhalte aus Spalte Q und in G12 nur Inhalte aus Spalte T stehen?

Vielen lieben Dank!!!!!!

[Werner.M]

Hallo,

weil du die Variable strWert bei deiner zweiten For Schleife einfach weiter benutzt. Die wird aber in der ersten Schleife ja schon gefüllt. Sprich die Werte aus der ersten For Schleife bleiben drin und die Werte aus der zweiten For Schleife kommen noch dazu.

Du mußt vor der zweiten For Schleife die Variable leeren.

Code:

strWert = ""

Gruß Werner

[schauan]

Hallöchen,

das mit dem Sortieren ist dafür gedacht, die Leerzeilen schnell auszugrenzen. Beim alphabetischen Sortieren stehen die Leerzellen dann je nach Sortierreihenfolge unterhalb der gefüllten Zellen.
Damit am Ende alles wieder in der ursprünglichen Reihenfolge steht, trage ich neben den Daten eine Zahlenreihe ein.

Anschließend sortiere ich nur den "gefüllten" Bereich anhand der Zahlenreihe und übernehme die Daten in ein Array. Das geht deutlich schneller, als jede Zelle einzeln zu prüfen und zu übernehmen.
Mit Join mach ich aus dem Array dann eine Zeichenkette und die trage ich in die Zelle A1 ein.

Deine Anfängeraufgabe wäre nun, die Zelladressen anzupassen. Mein Datenbereich ist z.B. A4 bis A10, Deiner S4 bis S5000. Also tust Du A4 zu S4 machen und aus A10 S5000. Steht nur A ohne Zahl, dann musst Du nur S draus machen. Gleiches Spiel mit B. Das wird dann zu T und die 10 zur 5000

Im Code müsste man nun nur noch eine dritte Sortierung machen, diesmal über alle Zellen der Spalte B oder dann T, damit die Leerzellen wieder an ihren Platz kommen.