Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 11:16:38
Pfade werden mit Anführungszeichen "" versehen
18.04.2024 11:09:53
for Schleife lässt leere Zeilen bei Abfrage mit Bedingung
18.04.2024 10:30:26
bedingte Formatierung lange Zahlen
18.04.2024 09:22:48
VBA Filter in intelligenter Tabelle funktioniert nicht
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1564to1568
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1564to1568
1564to1568
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

For each lässt Zelle aus. Warum?

For each lässt Zelle aus. Warum?
30.06.2017 16:17:34
Znuk
Hallo
Ich will in Excel eine Liste von Schlüsseln trennen. Die Liste sieht in etwa so aus:
[Key] Text
[Key] Text
[Key] Text
[Key] Text
DAfür habe ich folgendes Makro geschrieben: 

Sub texttrenner()
Dim rng As Range
For Each rng In Selection
With rng
.Offset(0, 1).Value = Mid(rng.Value, 2, InStr(1, rng, "]") - 2)
.Offset(0, 2).Value = Mid(rng.Value, InStr(1, rng, "]") + 2, 99)
.delete Shift:=xlToLeft
End With
Next rng
End Sub

Es schreibt immer die beiden Textteile in die Spalte rechts vom Ursprungstext und löscht dann den Ursprungstext und schiebt nach Links.
Soweit so gut, funktionert eigentlich bestens. Aber etwas ist komisch: Immer die zweite Zelle im markierten Bereich wird ausgelassen. Egal ob 3 oder 1000 Zellen markiert sind. Immer die zweite Zelle. Kann mir jemand erklären warum?
Key | Text
[Key] Text |
Key | Text
Key | Text

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: For each lässt Zelle aus. Warum?
30.06.2017 16:24:28
Hajo_Zi
ist doch klar da eine Zelle gelöscht wird, es fällt nicht mehr auf da nur 4 
Sub texttrenner()
Dim rng As Range
For Each rng In Selection
With rng
.Offset(0, 1).Value = Mid(rng.Value, 2, InStr(1, rng, "]") - 2)
.Offset(0, 2).Value = Mid(rng.Value, InStr(1, rng, "]") + 2, 99)
.delete Shift:=xlToLeft
End With
Next rng
End Sub

Ich gebe keinen Dank für eine Rückmeldung, da ich durch solche Beiträge nicht meine Beitragszahl erhöhen muss.
Rückmeldung ist ja in der Heutigen Zeit nicht üblich und die wenigen die eine Rückmeldung geben, mögen mir das verzeihen, das kein Danke für eine Rückmeldung kommt.
Anzeige
AW: For each lässt Zelle aus. Warum?
30.06.2017 16:32:32
Znuk
Bin mir nicht sicher, ob ich richtig verstehe oder verstanden worden bin. Hier noch eine Visualisierung:
Userbild
Immer die zweite Zelle im markierten Bereich wird nicht bearbeitet...
AW: For each lässt Zelle aus. Warum?
30.06.2017 16:34:19
Hajo_Zi
mein Code gefällt also nicht. Ich bin dann raus.
Gruß Hajo
AW: For each lässt Zelle aus. Warum?
30.06.2017 16:46:19
Nepumuk
Hallo Znuk,
ich finde Hajo's Antworten auch nur noch zum kotzen.
Dein Problem rührt daher, dass du die Zelle löschst. Teste mal:
Public Sub texttrenner()
    Dim rng As Range
    Dim strText As String
    For Each rng In Selection
        With rng
            strText = .Text
            .Value = Mid(strText, 2, InStr(1, strText, "]") - 2)
            .Offset(0, 1).Value = Mid(strText, InStr(1, strText, "]") + 2, 99)
        End With
    Next rng
End Sub

Gruß
Nepumuk
Anzeige
AW: For each lässt Zelle aus. Warum?
30.06.2017 17:00:46
Luschi
Hallo Znuk,
nimm diesen Befehl '.Delete Shift:=xlToLeft'
aus der For-Schleife raus und füge nach dem 'Next rng' ein:
Selection.Delete Shift:=xlToLeft
Hintergrund: der zu durchlaufende Bereich von Selection wird während der For-Schleifen-Initialisierung nur einmal in einer internen Range-Bereich-Variablen festgelegt (auf die der Programmierer keinen Einfluß hat) und nicht mehr angepaßt.
Wenn also der Bereich 'B6:B16' markiert ist, wird ja im 1. Durchlauf vor dem 'Next rng' die Zelle 'B6' gelöscht (nicht geleert!) und dafür rutscht die Zelle 'B7' nach. Das 'Next rng' schalten jetzt von 'B7' (und nicht von 'B6' nach 'B8' um, und deshalb wird jede 2. Zelle im SDelection-Bereich übersprungen.
Gruß von Luschi
aus klein-Paris
Mit ein paar Infomeldungen im Direktbereich des Vba-Editors, kannst Du es nachverfolgen - oder tausche Debug.Print gegen MsgBox aus.
Anzeige
AW: For each lässt Zelle aus. Warum?
30.06.2017 17:01:00
Luschi
Hallo Znuk,
nimm diesen Befehl '.Delete Shift:=xlToLeft'
aus der For-Schleife raus und füge nach dem 'Next rng' ein:
Selection.Delete Shift:=xlToLeft
Hintergrund: der zu durchlaufende Bereich von Selection wird während der For-Schleifen-Initialisierung nur einmal in einer internen Range-Bereich-Variablen festgelegt (auf die der Programmierer keinen Einfluß hat) und nicht mehr angepaßt.
Wenn also der Bereich 'B6:B16' markiert ist, wird ja im 1. Durchlauf vor dem 'Next rng' die Zelle 'B6' gelöscht (nicht geleert!) und dafür rutscht die Zelle 'B7' nach. Das 'Next rng' schalten jetzt von 'B7' (und nicht von 'B6' nach 'B8' um, und deshalb wird jede 2. Zelle im SDelection-Bereich übersprungen.
Gruß von Luschi
aus klein-Paris
Mit ein paar Infomeldungen im Direktbereich des Vba-Editors, kannst Du es nachverfolgen - oder tausche Debug.Print gegen MsgBox aus.
Anzeige
AW: For each lässt Zelle aus. Warum?
30.06.2017 17:23:33
Daniel
HI
das Problem wurde dir ja schon erklärt.
aber es gibt für diese Aufgabe eine bessere Lösung:
1. führe mit der Spalte die Funktion DATEN - DATENTOOLS - TEXT IN SPALTEN mit dem "]" als Trennzeichen aus.
2. lösche dann in der ersten Spalte das "[" mit ERSETZEN.
sieht als Code so aus 
With Selection
.TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, Other:=True, OtherChar:="]"
.Replace What:="[", Replacement:="", LookAt:=xlPart
End With

wenn du bei deinem Schleifencode bleiben willst, errechne doch zuerst den Text und den Key zuletzt.
dann kannst du den Ausgangswert mit dem Key überschreiben, weil du ihn nicht mehr benötigst und sparst dir damit das Delete.
außerdem solltest du, wenn du schon die With-Klammer verwendest, dies auch konsequent tun.
dh wenn im Code With rng steht, sollte die Variable rng bis zum End With nicht mehr auftauchen: 

Sub texttrenner()
Dim rng As Range
For Each rng In Selection
With rng
.Offset(0, 1).Value = Mid(.Value, InStr(1, .Value, "]") + 2, 99)
.Value = Mid(.Value, 2, InStr(1, .Value, "]") - 2)
End With
Next rng
gruß Daniel
Anzeige
AW: For each lässt Zelle aus. Warum?
30.06.2017 17:44:35
Werner
Hallo,
und jetzt auch noch eine Möglichkeit von mir. Die Schleife von hinten nach vorne laufen lassen.
Public Sub Texttrenner()
Dim strText As String
Dim loLetzte As Long
Dim i As Long
Application.ScreenUpdating = False
'Tabellenblatt anpassen
With Worksheets("Tabelle1")
'letzte belegte Zelle in Spalte A ermitteln
loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = loLetzte To 3 Step -1
strText = .Cells(i, 1)
.Cells(i, 2) = Mid(strText, 2, InStr(1, strText, "]") - 2)
.Cells(i, 3) = Mid(strText, InStr(1, strText, "]") + 2, 99)
.Cells(i, 1).Delete shift:=xlToLeft
Next i
End With
Application.ScreenUpdating = True
End Sub
Gruß Werner
Anzeige
Danke Euch allen!
03.07.2017 08:18:54
Znuk
Es war nach jahrelangem Mitlesen meine allererste Frage. Mit Erfolg. Danke!
Gerne u. Danke für die Rückmeldung. o.w.T.
03.07.2017 13:49:58
Werner

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige