Anzeige
Archiv - Navigation
1820to1824
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
Inhaltsverzeichnis

Hilfestellung For To Zeilenreferenzen

Hilfestellung For To Zeilenreferenzen
26.03.2021 19:19:04
Richi
Ciao Zäme
Ich hoffe auf eure Hilfe.
Ich krieg den Dreh nicht raus mit meinen Zeielreferenzen die Zellen richtig zu befüllen.
Ausgangslage:
Tabelle "Quelle" zeigt in den Spalten W01-W12 Werte auf in Spalte B die Revision und Spalte F das Dokument mit welchem eine Reparatur ausgeführt wurde.
Ziel:
In jeder Zelle W01-W12 in welcher ein Wert hinterlegt ist, soll in Tabelle "Ziel" das entsprechende Dokument aus Spalte F eingesetzt werden.
Final sollen diese pro Revision unter der jeweiligen W01-W12 untereinander aufgelistet werden ohne dazwischenliegende Leerzellen.
Mit meinem Code krieg ich dies Teilweise hin, jedoch überschreibt es mir einige Zellen und ich finde nicht raus warum das so ist.
PS: In Tabelle "So sollte es sein" hab ich die Finale richtige Auslistung hinterlegt
Hier der Link zum File: https://www.herber.de/bbs/user/145148.xlsm
Musste dieses Abspecken Originalfile enthält über 3000 Zeilen
Freue mich auf eure Hilfestellung.
Liebe Gruess
Richi
Option Explicit
<pre>Sub Tasks_pro_Revision_Woche()
Dim wb As Workbook
Dim wsQ As Worksheet
Dim wsZ As Worksheet
Dim sheetnew As Worksheet
Dim Sheet As Worksheet
Dim lZQ, lZZ, lSQ, lSZ As Long 'Letzte Zeilen, Spalten aus Quelle und Ziel
Dim StartSQ As Long 'Start Spalte Quelle
Dim StartZQ As Long 'Start Zeile Quelle
Dim StartSZ As Long 'Start Spalte Ziel
Dim StartZZ As Long 'Start Zeile Ziel
Dim Endespalte As Long ' Ende derjenigen Spalten die zu Zählen sind
Dim EZGrp As Long 'Zähler Ende einer Gruppe Ziel
Dim ZZZ As Long 'Zeilenzähler Ziel
Dim ZNGrp 'Zeilenreferenz neue Gruppe
Dim i As Integer
Dim j As Integer
Set wb = ThisWorkbook
Set wsQ = wb.Worksheets("Quelle")
Set wsZ = wb.Worksheets("Ziel")
'-----------Referenz Startpunkte für Spalten Zeilen in Quelle und Ziel---------------
StartSQ = 13
StartZQ = 2
StartSZ = 3
StartZZ = 2
Endespalte = 18 'Letzt zu Zählende Spalte bezüglich Zelleninhalte
lZQ = wsQ.Cells(wsQ.Rows.Count, 1).End(xlUp).Row
lZZ = wsZ.Cells(wsZ.Rows.Count, 1).End(xlUp).Row
lSQ = wsQ.Cells(1, wsQ.Columns.Count).End(xlToLeft).Column
lSZ = wsZ.Cells(1, wsZ.Columns.Count).End(xlToLeft).Column
'-----------Daten löschen---------------
wsZ.Select
wsZ.Range("A2:DD15000").Select
Selection.ClearContents
wsZ.Range("A2").Select
For i = StartZQ To lZQ
EZGrp = wsZ.UsedRange.Rows.Count 'Letzte beschriebene Zeile des Ranges in Ziel
If wsQ.Cells(i, 2).Value = wsZ.Cells(wsZ.Rows.Count, 2).End(xlUp).Value Then 'Vergleich Zeile Quelle mit letzter Zeile Ziel Reg gleich
If wsQ.Application.WorksheetFunction.CountA(Range(wsQ.Cells(i, StartSQ), wsQ.Cells(i, Endespalte))) > 0 Then 'Prüfen ob Quellen Zeile einen Eintrag hat
For j = StartSQ To Endespalte 'Alle Spalten einer Zeile durchlaufen und Daten eintragen
If wsQ.Cells(i, j) <> "" Then 'Wenn in neuer Gruppe eine Zelle in erster Zeile leer ist
wsZ.Cells(wsZ.Cells(wsZ.Rows.Count, j - (StartSQ - StartSZ)).End(xlUp).Row + 1, 1).Value = wsQ.Cells(i, 1) 'In erste freie Zeile der Spalte schreiben
wsZ.Cells(wsZ.Cells(wsZ.Rows.Count, j - (StartSQ - StartSZ)).End(xlUp).Row + 1, 2).Value = wsQ.Cells(i, 2)
wsZ.Cells(wsZ.Cells(wsZ.Rows.Count, j - (StartSQ - StartSZ)).End(xlUp).Row + 1, j - (StartSQ - StartSZ)).Value = wsQ.Cells(i, 2) & " - " & wsQ.Cells(i, 6) & " - " & wsQ.Cells(i, 7)
End If
Next j
End If
Else
If wsQ.Cells(i, 2).Value <> wsZ.Cells(wsZ.Rows.Count, 2).End(xlUp).Value Then 'Vergleich Zeile Quelle mit letzter Zeile Ziel Reg ungleich
ZNGrp = wsZ.UsedRange.Rows.Count + 1 'Referenzzeile Start Gruppe
If wsQ.Application.WorksheetFunction.CountA(Range(wsQ.Cells(i, StartSQ), wsQ.Cells(i, Endespalte))) > 0 Then 'Prüfen ob Quellen Zeile einen Eintrag hat
For j = StartSQ To Endespalte 'Alle Spalten einer Zeile durchlaufen und Daten eintragen
If wsQ.Cells(i, j) <> "" Then 'Wenn in neuer Gruppe eine Zelle in erster Zeile leer ist
wsZ.Cells(ZNGrp, 1).Value = wsQ.Cells(i, 1)
wsZ.Cells(ZNGrp, 2).Value = wsQ.Cells(i, 2)
If wsZ.Cells(ZNGrp, j - (StartSQ - StartSZ)) = "" Then
wsZ.Cells(ZNGrp, j - (StartSQ - StartSZ)).Value = wsQ.Cells(i, 2) & " - " & wsQ.Cells(i, 6) & " - " & wsQ.Cells(i, 7)
Else
' wsZ.Cells(wsZ.Cells(wsZ.Rows.Count, j - (StartSQ - StartSZ)).End(xlUp).Row + 1, 1).Value = wsQ.Cells(i, 1) 'In erste freie Zeile der Spalte schreiben
' wsZ.Cells(wsZ.Cells(wsZ.Rows.Count, j - (StartSQ - StartSZ)).End(xlUp).Row + 1, 2).Value = wsQ.Cells(i, 2)
wsZ.Cells(wsZ.Cells(wsZ.Rows.Count, j - (StartSQ - StartSZ)).End(xlUp).Row + 1, j - (StartSQ - StartSZ)).Value = wsQ.Cells(i, 2) & " - " & wsQ.Cells(i, 6) & " - " & wsQ.Cells(i, 7)
End If
End If
Next j
End If
End If
End If
Next i
End Sub</pre>

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Hilfestellung For To Zeilenreferenzen
27.03.2021 01:30:27
Yal
Hallo Richi,
sind die JobCard jede eindeutig, spricht keine JobCard wiederholt sich, oder ist es nur deine Beispiel geschuldet? Oder mindestens pro AP(Arbeitspaket?) eindeutig?
Nehmen wir JobCard & AP bildet eine eindeutige Schlüssel, dann geht es nur darum, pro Schüssel die Wert in W1 bis W6 durch die Verkettung Reg - JobCard - KurzText Auftrag zu ersetzen. Wenn in W1 bis W6 keine Werte, dann keine Übernahme.
Aber das wäre zu einfach. Weil dann passiert, was in Datenbanken ein "Group by" heissen könnte (nur dass Group-by Summen, Mittelwert, Minoder Max erzeugt):
Hans 2 - Zeile 4 (W1) und Hans 2 - Zeile 5 (W2) werden zusammengebracht sowie
Hans 2 - Zeile 6 (w1) und Hans 2 - Zeile 7 (W2).
Warum nicht
Hans 2 - Zeile 4 (w1) und Hans 2 - Zeile 7 (W2).
Hans 2 - Zeile 6 (w1) und Hans 2 - Zeile 5 (W2).
?
Ist die originale Reihenfolge der Einträge die einzige Grund? Das ist der Grund warum Group-by nur auf Zahlen sich anwenden lässt.
(Abgesehen davon, dass ich die "Kompression" im Sinn der Wertigkeit der Information nicht nachvollziehen kann)
Genau diesen Punkt ist der Grund, warum die einfache, logische Verarbeitung in den Wannsinn umkippt.
Das sieht man an "Kurt 70": wären die Werte von Job "Zeile 34" nicht "Zeile 34" sondern "Zeile 31", wie würdest Du den 3 von "Zeile 29" zusammen mit den 5,1 und 39,1 von "Zeile 33" zusammenbringen?
Eine bereinigte Version des ursprunglichen Codings wurde ohne Kompression so aussehen:
Sub Tasks_pro_Revision_Woche()
Dim wsQ As Worksheet
Dim wsZ As Worksheet
Dim zR As Long       'Ziel-Row(Zeile)
Dim r As Integer     'Row-Variable
Dim c As Integer     'Column-Variable
'----Referenz Startpunkte für Spalten Zeilen in Quelle und Ziel----
Const Wa = 13 'Anfangsspalte der Wxx
Const Wx = 21 'Endspalte der Wxx (a-z für Anfang-Ende. Aber z vermeiden, weil Ziel. e für Ende  _
ist zu ähnlich als a)
Const T = " - " 'Trenner
'----Init----
Set wsQ = ThisWorkbook.Worksheets("Quelle")
Set wsZ = ThisWorkbook.Worksheets("Ziel2")
wsZ.Range("A2:DD15000").ClearContents 'Zielbereich leeren
'Für alle Zeile der Quelle
For r = 2 To wsQ.Cells(wsQ.Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountA(wsQ.Cells(r, Wa).Resize(1, 12)) > 0 Then
zR = wsZ.Cells(99999, 1).End(xlUp).Row + 1 'Nr erste freie Zeile ermitteln
wsZ.Cells(zR, 1) = wsQ.Cells(r, 1)
wsZ.Cells(zR, 2) = wsQ.Cells(r, 2)
For c = Wa To Wx
If wsQ.Cells(r, c)  "" Then _
wsZ.Cells(zR, c - Wa + 3) = wsQ.Cells(r, 2) & T & wsQ.Cells(r, 6) & T & wsQ. _
Cells(r, 7)
Next c
End If
Next r
End Sub
Variablen sind gut fürs debuggen, aber es gilt je weniger desto besser. ThisWorkbook kommt nur zweimal vor: keine Variable.
Lieber kürzere Variable. Je öfter die Variable verwendet wird, desto kürzer soll sie sein. Aber trotzdem "sprechend": r und c anstatt i und j.
Quelle und Ziel mit Q und Z: seht gut. Dann aber Zeile nicht mit Z! Lieber r und c (in dem Fall qR, zR, qC, zC, wobei nur zR notwendig ist).
Variable, dessen Wert sich währen den ganzen Anblauf nicht ändert als Konstante definieren. Definition und Initialisierung ist dann zusammengefasst.
Kommentar: soviel wie nötig, so wenig wie möglich. Zu lange Kommentar am Ende eine Zeile versäumt den Strg+Ende (na gut: bin Tastatur-Junky).
Wenn If ein Zustand prüft, muss nach den Else nicht den Gegenzustand geprüft werden.
Das Zusammenfassen von ähnliche Blöcke ("Faktorisieren"): schwierig. Erfahrung, Übung. Faktorisieren, weil wie in Mathe: aus ax + ay, a(x + y) machen!
Ich hoffe, die paar Tipps können Dir helfen auf dem Weg von "VBA bescheiden" zu "VBA Profi".
Viel Erfolg
Yal

Anzeige
AW: Hilfestellung For To Zeilenreferenzen
27.03.2021 10:02:59
Richi
Hi Yal
Besten Dank für deine Hilfestellung.
Franz konnte mir helfen.
Bei deinem Code wurde jeweils eine neue Zeile geschrieben und nicht die erste leere einer Gruppe.
Nehme mir deinen Ratschlag, weniger Referenzvariablen verwenden zu Herzen.
Liebe Gruess
Richi

AW: Hilfestellung For To Zeilenreferenzen
27.03.2021 03:00:19
fcs
Hallo Richi,
deine Problem stecken hier.
wsZ.Cells(wsZ.Cells(wsZ.Rows.Count, j - (StartSQ - StartSZ)).End(xlUp).Row

Wenn in der Spalte oberhalb der Zeile, in der Werte eingetragen werden sollen, Leerzellen sind, dann liefert der Ausdruck für diese Spalte eine falsche Zeilennummer.
Die Zeile, in die eingetragen werden soll, muss vorher ermittelt/festgelegt und in einer Variablen gespeichert werden.
Ich habe dein Makro mal etwas umgestrickt. Es scheint jetzt zu funktionieren.
https://www.herber.de/bbs/user/145151.xlsm
LG
Franz

Anzeige
AW: Hilfestellung For To Zeilenreferenzen
27.03.2021 10:00:07
Richi
Ganz herzlichen Dank Franz
Deine Lösung funktioniert super.
Genau so hab ich mir das im Kopf vorgestellt.
Muss da nochmals über die Bücher bezüglich Zellen referenzieren .
Cool
Liebe Gruess Richi

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige