Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1876to1880
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

Makro führt nicht bis zum Schluss aus

Makro führt nicht bis zum Schluss aus
28.03.2022 15:24:45
Sabine
Hallo ihr Lieben,
ich hatte bereits vor einer Woche einen Thread geöffnet, dieser hier:
https://www.herber.de/forum/cgi-bin/callthread.pl?index=1876210
Ich habe nun den Fehler gefunden, der dazu führt, dass sich der Bezug der Formeln ändert.
Beim Aktualisieren der Tabelle verrutscht der Zellbezug der letzten Zeile des rechten Bereiches (welcher nicht zur formatierten Tabelle gehört) auf die neue letzte Zeile des aktualisierten Datenbereiches. Das passiert aber nur bei der letzten Zeile.
Also möchte ich nun, dass das Makro die letzte Zeile löscht und die Zeile zuvor bis nach unten kopiert.
Hier ist die Datei:
https://www.herber.de/bbs/user/152112.xlsm
Mein neuer Code lautet:

Sub Formeln()
Dim i As Integer
Dim j As Integer
Dim Sp As Integer
Application.ScreenUpdating = False
i = 6729 'Zeile, von wo aus gestartet wird = Counter, wie viele Zeilen
j = 6724 'Zeile, von wo aus gestartet wird = Counter, wie viele Zeilen
Sp = 115 'Spalte, von wo aus gestartet wird = Counter, wie viele Spalten
Do Until ActiveSheet.Range("A" & i).Value = "" 'Spalte A Zeilen zählen
i = i + 1
Debug.Print i
Loop
Do Until ActiveSheet.Range("K" & j).Value = "" 'Spalte K
j = j + 1
Debug.Print j
Loop
Do Until ActiveSheet.Cells(1, Sp).Value = "" 'In Zeile eins bei den Überschriften schauen
Sp = Sp + 1
Debug.Print Sp
Loop
If i > j Then 'so lange DB länger ist als Berechnung daneben
Cells(j - 1, 11).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Do Until i + 1 = j + 1 'bis die Zeilen gleich sind
ActiveSheet.Range(Cells(j - 2, 11), Cells(j - 2, Sp - 1)).Select 'ab Spalte K (= 11) Zeilen markieren bis letzte Spalte = Counter Sp
Selection.Copy
j = j + 1
Range(Cells(j - 2, 11), Cells(j - 2, Sp - 1)).Select
Selection.PasteSpecial xlPasteFormulas
Debug.Print j
Loop
End If
Application.ScreenUpdating = True
End Sub
Das Problem ist jetzt, dass die letzte Zeile ohne Formel bleibt - und ich weiß nicht wie ich das ändern kann. habe bereits bei Do Until von i-1=j-1 geändert auf i+1=j+1, aber das debug.print zeigt an, dass das j bei der letzten Zeile bleibt und dann die If Formel verlassen wird. Kann mir da jemand helfen?
Rudi hatte übrigens letztes Mal diesen Code gepostet:

Sub Formeln2()
Dim lngD As Long, lngF As Long, lngC As Long
lngD = Cells(Rows.Count, 1).End(xlUp).Row 'Spalte A
lngF = Cells(Rows.Count, 11).End(xlUp).Row 'Spalte K
lngC = Cells(1, Columns.Count).End(xlToLeft).Column 'Zeile 1 - Anzahl Spalten
If lngD 
Aber wie das so ist bei fremden Codes, weiß ich natürlich nicht, wie ich das hier einbauen muss, weil ich es auch nur bedingt verstehe.
Zudem möchte ich auch gerne wissen, was ich bei meinem ändern muss.
Danke und liebe Grüße,
Sabi

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro führt nicht bis zum Schluss aus
28.03.2022 15:44:34
Sabine
PS: Die Counter habe ich so hoch eingestellt, damit ich nicht so lange warten muss, bis sie am Ende angekommen sind...im Original stehen sie bei 1
AW: Makro führt nicht bis zum Schluss aus
28.03.2022 18:34:57
Herbert_Grom
Hallo Sabine,
was soll in den Spalten N - Q erscheinen? Kannst du mir mal ein paar manuelle Beispiele machen?
Servus
AW: Makro führt nicht bis zum Schluss aus
28.03.2022 20:00:47
Yal
Hallo Sabine,
folgende Code sollte genau dasselbe machen, wie deinen.
Wenn es um die Anzahl an Zielen in Spalte A und K, kann man die Variable a und k nennen anstatt i und j.

Sub Formeln()
Dim a As Integer
Dim k As Integer
Dim Sp As Integer
Application.ScreenUpdating = False
a = Cells(Rows.Count, "A").End(xlUp).Row 'Spalte A Zeilen zählen, bzw. letzte Zeile finden
k = Cells(Rows.Count, "K").End(xlUp).Row 'Spalte K Zeilen zählen, bzw. letzte Zeile finden
Sp = Cells(1, Columns.Count).End(xlToLeft).Column 'Anzahl Spalten in erste Zeile ermitteln
Debug.Print a
Debug.Print k
Debug.Print Sp
Range(Cells(k + 1, "K"), Cells(99999, 999)).ClearContents
Cells(k, "K").Resize(1, Sp - 10).Copy 'mindestens eine Spalte (K ist Spalte 11)
Cells(k + 1, "K").Resize(a - k, Sp - 10).PasteSpecial xlPasteFormulas
Application.ScreenUpdating = True
End Sub
Wenn Du die Details nicht verstehst, aber diese kennenlernen möchtest, einfach nachfragen.
VG
Yal
Anzeige
letzte Zeile löschen und dann copy+paste
29.03.2022 08:25:13
Sabine
Hallo Herbert,
ab Spalte N sind Formeln. In der Originaldatei sind an der Tabelle zusätzlich 109 Spalten, 3+2x53. Deshalb gehören diese nicht zur formatierten Tabelle (die Datei wird dann zu groß und langsam).
Ich möchte das die Formeln bis dahin kopiert werden, wo die formatierte Tabelle endet, da ich mich dann später wiederum auf diese hinten angehangenen Formeln beziehe. Die formatierte Tabelle beinhaltet Daten, die ich wöchentlich aktualisiere, wodurch sie sich verlängert. Dann sollen die Formeln hinten dran so weit nach unten kopiert werden, bis die Tabelle davor endet.
Hallo Yal,
danke für den Code. Ich möchte die Bereiche gerne variabel halten, d.h. also Cells(99999, 999) ersetzen mit variablen Komponenten. Zudem reicht es meiner Ansicht nach aus, die letzte Zeile zu löschen. Der ganze Bereich könnte auch gelöscht werden, muss aber nicht.
LG
Sabi
Anzeige
AW: letzte Zeile löschen und dann copy+paste
29.03.2022 10:13:48
Yal
Hallo Sabine,
"variabel" ist immer gut, aber in dem Fall ein nutzlose Luxus.
Man kann eine teuere und komplizierte Methode verwenden, um zu ermitteln, welche Zellen befüllt sind, und nur diese löschen (=Inhalt löschen, = leeren).
Aber leere Zellen zu leeren, ist besonders günstig, da Excel die Optimierung eh selber vornimmt.
VG
Yal
AW: letzte Zeile löschen und dann copy+paste
29.03.2022 10:31:15
Herbert_Grom
Hallo Sabine,
da ich des Lesens mächtig bin, habe ich deine Frage ja gelesen und weiß, dass du die Formeln kopiert haben willst. Aber du hast meine Frage leider nicht richtig gelesen, zumindest sie nicht wie gewünscht beantwortet. Also, lies sie noch mal durch.
Servus
Anzeige
AW: letzte Zeile löschen und dann copy+paste
29.03.2022 10:57:29
Sabine
Hallo Herbert,

Was soll in den Spalten erscheinen
- wenn du damit meinst, was das gewünschte Ergebnis aus den Formeln ist --> es ist die Anzahl der Abwesenheitstage. Die ersten 53 Spalten sind Abwesenheiten, die nicht Feiertage sind und die 53 Spalten danach sind Feiertage. 53 Spalten jeweils weil 53 Wochen pro Jahr (inkl. Schaltjahr).
Beantwortet das deine Frage?
Ansonsten konkretisiere sie bitte, denn dann verstehe ich sie nicht.
LG
Sabi
AW: letzte Zeile löschen und dann copy+paste
30.03.2022 08:12:14
Sabine
Guten Morgen,
ich möchte nur kurz mein Ergebnis zeigen.
Diese Nacht ist mir die Lösung eingefallen.

Sub Formeln()
Dim i As Integer
Dim j As Integer
Dim Sp As Integer
Application.ScreenUpdating = False
i = 1 'Zeile, von wo aus gestartet wird = Counter, wie viele Zeilen
j = 1 'Zeile, von wo aus gestartet wird = Counter, wie viele Zeilen
Sp = 1 'Spalte, von wo aus gestartet wird = Counter, wie viele Spalten
Do Until ActiveSheet.Range("A" & i).Value = "" 'Spalte A Zeilen zählen
i = i + 1
Loop
Do Until ActiveSheet.Range("K" & j).Value = "" 'Spalte K
j = j + 1
Loop
Do Until ActiveSheet.Cells(1, Sp).Value = "" 'In Zeile eins bei den Überschriften schauen
Sp = Sp + 1
Loop
If i > j Then 'so lange DB länger ist als Berechnung daneben
j = j - 1
Cells(j, 11).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Do Until i - 1 = j - 1 'bis die Zeilen gleich sind
ActiveSheet.Range(Cells(j - 1, 11), Cells(j - 1, Sp - 1)).Select 'ab Spalte K (= 11) Zeilen markieren bis letzte Spalte = Counter Sp
Selection.Copy
j = j + 1
Range(Cells(j - 1, 11), Cells(j - 1, Sp - 1)).Select
Selection.PasteSpecial xlPasteFormulas
Loop
End If
Application.ScreenUpdating = True
End Sub
Ich muss natürlich den Zähler um eins reduzieren. Und dann funktioniert es.
LG Sabi
Anzeige
AW: letzte Zeile löschen und dann copy+paste
30.03.2022 09:50:23
Yal
Hallo Sabine,
mathematisch gesehen, kannst Du folgende Konstrukt

Do Until i - 1 = j - 1
in

Do Until i = j 
Es ist genauso gleich.
Aber ich gehe davon aus, dass Du eher auf deine eigene Lösung festhalten wird.
VG
Yal
AW: letzte Zeile löschen und dann copy+paste
30.03.2022 10:00:39
Sabine
Hallo Yal,
ja das ist möglich.
Ich versuche es mal.
Ggf. wird dann die Formel eins zu weit kopiert, d.h. eine Zeile weiter als die Daten aus der DB gehen.
Das liegt daran, dass er schaut, wann in der Zelle nichts steht durch das .Value = "". Und das ist eine Zeile unter dem Datensatz der Fall. Den Wert nimmt das i dann an.
Infolgedessen habe ich es glaube ich mal geändert auf das minus eins. Damit er nur bis zum Datensatz geht und nicht noch eins weiter darunter.
LG
Sabi.
Anzeige
AW: letzte Zeile löschen und dann copy+paste
30.03.2022 11:00:43
Yal
Hallo Sabine,
Du solltest beherzigen, was Herbert gesgt hat: richtig lesen hilft.
Meine vorige Anmerkung bezog sich nur auf "Do Until".
Deine Begründung zeigt, dass Du dich mit meiner Vorschlag nicht ausseinander gesetzt hast. Es vernichtet jede Motivation, Dir zur Hilfe zu kommen.
Ein schöner Tag noch.
VG
Yal
AW: letzte Zeile löschen und dann copy+paste
30.03.2022 11:49:19
Sabine
Hallo Yal,
den Gedankengang kann ich jetzt nicht nachvollziehen.
Ich glaube es ist eine Sache zu lesen und eine andere zu verstehen und dann wiederum den Gedankengang des anderen nachzuvollziehen.
Möglich, dass ich begriffsstutzig bin und deshalb das gelesene falsch interpretiere - deshalb sollte man jemanden meiner Ansicht nach aber nicht aufgeben, sondern überlegen, wie man genauer beschreiben kann, was gemeint ist.
Hier in dem Fall bspw. bin ich davon ausgegangen, dass das Do Until dem Code sagt, bis wann er die Zeilen kopieren + einfügen soll. Deshalb meine Erläuterung dazu. Das ist anscheinend nicht korrekt.
Wenn es ungern gesehen ist, zu versuchen zu verstehen, was der Code macht und deshalb eine Erläuterung zu geben bzw. in eine Konversation zu gehen, dann bin ich tatsächlich die falsche Person. Ich mache nicht einfach copy+paste und sage Danke, lieb von dir.
Ich gebe meine Ansicht der Dinge zurück, die natürlich auch falsch sein kann. Sehe ich dann auch ein.
So, zur Beruhigung, da steht nun i=j.
Im Kommentar dahinter habe ich gesetzt, dass es ein nützlicher und netter Hinweis von Yal war.
Ist schon recht amüsant zu überlegen, dass so eine Kleinigkeit zur Aufgabe führt.
Aber wahrscheinlich bin ich auch nur wieder zu begriffsstutzig um das eigentliche Problem dahinter zu erkennen.
LG
Danke
Sabi.
Anzeige
Rudis Code ist bereits perfekt
31.03.2022 08:59:25
Sabine
Guten Morgen,
so, jetzt habe ich es endlich verstanden.
Also in Rudis Code muss ich das Löschen gar nicht einfügen, da ja Zeile zwei genommen und nach unten kopiert wird.
Das führt dazu, dass der Fehler, der ggf. am Ende oder gar bereits zuvor eintritt bzw sich einschleicht, sofort eliminiert wird.
Sehr schlau.
Das ist natürlich praktisch.
Das heißt ein Clearcontents zuvor macht gar keinen Sinn bzw ist unnötig.
Das hätte doch mal jemand sagen können....aber vllt ist das auch passiert und ich habe es nur wieder nicht verstanden...das ist wohl der wahrscheinlichere Fall.
Jetzt kann ich also meinen so umstellen, dass er wie bei Yal ab Zeile drei löscht und dann nach unten kopiert und damit das gleiche macht.
LG
Sabi.
Anzeige

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige