Anzeige
Archiv - Navigation
1296to1300
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

Simples Makro gesucht - Zeile runterziehen(Formel)

Simples Makro gesucht - Zeile runterziehen(Formel)
19.02.2013 09:22:00
Arthur
Guten Morgen zusammen
Ich habe hier wiederum eine andere grosse Liste. Jetzt habe ich ein Ressourcenproblem (Formel laden nicht, Excel schliesst sich wegen überlastung).
Ich habe jetzt aber alle Formeln gelöscht (bis auf eine Zeile) und wenn ich nun:
D8:APX8 markiere, dann ein bisschen runterziehe z.b bis etwa Zeile 15
dann ist Excel nicht überlastet und lädt brav die Formeln. Da ziehe ich von D15:APX8 wieder ein bisschen nach unten etc. Es funktioniert so.
Nun habe ich das Problem, dass ich aber 15'000 Zeilen habe, bis ich step by step dies runtergezogen habe bin ich 60 Jahre alt. Könnte mir jemand mit einem Makro aushelfen, welches bei D8:APX8 (markiert) anfängt, immer ca 10 Zellen runterzieht UND WICHTIG ERST wenn Excel die neuen Formel/Verlinkungen geladen hat, WEITER um 10 Zeilen runterzieht. Das kann von mir aus 10 STunden dauern, aber ich muss nichts manuel machen.
Ich bin echt froh, das ich so jetzt eine Lösung gefunden habe und mir Excel so nicht abstürtzt, nur halt das Makro fehlt mir, habe keine Kenntnisse in VBA.
Vielen dank für eure Hilfe!!
Gruss
Arthur

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Simples Makro gesucht - Zeile runterziehen(Formel)
19.02.2013 09:30:24
Rudi
Hallo,
das sind fast 17 Mio. Formeln.
Was bringt dich zu der Annahme, dass Excel z.B. ab Zeile 1000 nicht abschmiert?
Gruß
Rudi

AW: Simples Makro gesucht - Zeile runterziehen(Formel)
19.02.2013 09:39:59
Klaus
Hi Arthur,
dieses Makro kopiert die Formeln Zeile-für-Zeile, erzwingt jedesmal eine Berechnung UND überschreibt die berechneten Formeln mit Werten, um spätere Rechenzeit zu sparen. (wenn du das nicht willst, kannst du die dafür zuständige Zeile aber auskommentieren).
Damit du nicht einsam und verlassen vor dem Rechner sitzt und dich fragst ob Excel noch arbeitet oder schon abgeschmiert ist, habe ich eine Statusmeldung eingebaut (Statusleiste = ganz unten links vom Excelfenster).
Option Explicit
Sub StepByStep()
Dim lRowFirst As Long
Dim lRowLast As Long
Dim lCount As Long
Dim iColFirst As Integer
Dim iColLast As Integer
lRowFirst = 8 'ab Zeile 8
lRowLast = 15000 'bis Zeile 15000 gemäß Aufgabe
iColFirst = 4 'Spalte D
iColLast = 1116 'Spalte APX
Application.ScreenUpdating = False 'Bildschirm ruhigstellen (spart Zeit!)
With ActiveSheet
For lCount = lRowFirst To lRowLast
'Rückmeldung an User dass Excel noch lebt / noch arbeitet
Application.StatusBar = "Schreibe Zeile " & lCount & " von " & lRowLast
'Formeln kopieren
.Range(.Cells(lCount, iColFirst), .Cells(lCount, iColLast)).Copy
'Formeln eine Zeile weiter unten einfügen
.Range(.Cells(lCount + 1, iColFirst), .Cells(lCount + 1, iColLast)).PasteSpecial
'berechnen erzwingen - kan, dass du diese Zeile nicht brauchst. Ausprobieren!
Calculate
'obere Formelzeile durch Werte ersetzen - eventuell auskommentieren!
.Range(.Cells(lCount, iColFirst), .Cells(lCount, iColLast)).Value = .Range(.Cells( _
lCount, iColFirst), .Cells(lCount, iColLast)).Value
Next lCount
End With
Application.ScreenUpdating = True
Application.StatusBar = ""
End Sub
Grüße,
Klaus M.vdT.

Anzeige
AW: Simples Makro gesucht - Zeile runterziehen(Formel)
19.02.2013 09:54:08
Arthur
Hallo Rundi und Klaus
Es ist mir klar, dass dies eine riese LIste ist. Bin aber im Büro und da haben wir eigentlich gute Server. Trotzdem reicht es nicht aus :-)
Vielen Dank Klaus für dein Makro, das werde ich gleich jetzt testen.
Viele Grüsse
ARthur

AW: Simples Makro gesucht - Zeile runterziehen(Formel)
19.02.2013 14:43:39
Klaus
Hi Arthur,
sind noch keine 10, aber fast 6 Stunden um. Läuft das Makro noch?
Grüße,
Klaus M.vdT.

AW: Simples Makro gesucht - Zeile runterziehen(Formel)
19.02.2013 14:43:40
Klaus
Hi Arthur,
sind noch keine 10, aber fast 6 Stunden um. Läuft das Makro noch?
Grüße,
Klaus M.vdT.

AW: Simples Makro gesucht - Zeile runterziehen(Formel)
20.02.2013 01:06:58
Arthur
Hallo Klaus
Ich habe es getestet mit ein paar Zeilen und es hat funktioniert! Vielen herzlichen Dank!!
Jetzt versuche ich es noch mit allen Zeilen und das Makro so zu ändern, das es nicht Werte ergibt, sondern immer noch die FOrmeln bleiben. Ev. wird noch was geändert in Zukunft.
Aber nohcmals vielen Dank, echt! Retter in der Not. Ich melde mich nochmals, obs beim zweiten Anlauf auch geklappt hat.

Anzeige
AW: Simples Makro gesucht - Zeile runterziehen(Formel)
20.02.2013 08:04:34
Klaus
das es nicht Werte ergibt, sondern immer noch die FOrmeln bleiben.
Easy - einfach die fett formatierten Zeilen löschen und / oder auskommentieren.
Option Explicit
Sub StepByStep()
Dim lRowFirst As Long
Dim lRowLast As Long
Dim lCount As Long
Dim iColFirst As Integer
Dim iColLast As Integer
lRowFirst = 8 'ab Zeile 8
lRowLast = 15000 'bis Zeile 15000 gemäß Aufgabe
iColFirst = 4 'Spalte D
iColLast = 1116 'Spalte APX
Application.ScreenUpdating = False 'Bildschirm ruhigstellen (spart Zeit!)
With ActiveSheet
For lCount = lRowFirst To lRowLast
'Rückmeldung an User dass Excel noch lebt / noch arbeitet
Application.StatusBar = "Schreibe Zeile " & lCount & " von " & lRowLast
'Formeln kopieren
.Range(.Cells(lCount, iColFirst), .Cells(lCount, iColLast)).Copy
'Formeln eine Zeile weiter unten einfügen
.Range(.Cells(lCount + 1, iColFirst), .Cells(lCount + 1, iColLast)).PasteSpecial
               'berechnen erzwingen - kan, dass du diese Zeile nicht brauchst. Ausprobieren! _
'Calculate
'obere Formelzeile durch Werte ersetzen - eventuell auskommentieren!
'.Range(.Cells(lCount, iColFirst), .Cells(lCount, iColLast)).Value = .Range(.Cells( _
lCount, iColFirst), .Cells(lCount, iColLast)).Value
Next lCount
End With
Application.ScreenUpdating = True
Application.StatusBar = ""
End Sub
Wenn deine Formeln volatil sind, könnte das aber die Berechnungszeit drastisch verlängern. Sind deine Formeln nicht volatil und greifen auch nicht auf den Bereich zu, in den die Formeln kopiert werden, dann dürfte sich die Berechnungszeit nicht deutlich ändern.
Grüße,
Klaus M.vdT.

Anzeige
AW: Simples Makro gesucht - Zeile runterziehen(Formel)
21.02.2013 08:34:17
Arthur
Hallo Klaus
SO, jetzt habe ich das komplett getestet und umgesetzt, es funktioniert hervorragend. Ebenfalls in der Form, dass ich keine Werte erhalte, sondern die Formeln bestehen bleiben.
Vielen herzlichen Dank, du glaubst gar nicht wie du mir geholfen hast!
Gruss
Arthur

Danke für die Rückmeldung! mit Text.
21.02.2013 09:22:02
Klaus
Hallo Arthur,
danke dass du dich auch nach zwei Tagen noch zurückmeldest! Das ist ja heutzutage leider kaum noch üblich ...
Ich glaube allerdings zu wissen wie ich dir geholfen habe :-) Mit Monsterdateien muss ich öfter arbeiten. Wir haben hier solche scherzhaften Abfragen wie "tausend Sachnummern, je im Schnitt zehn Produktionsschritte, je im Schnitt fünf Kunden, jeweils die nächsten dreißig Bestellungen, eine Zeile pro Bestellung" - das haut dann mal eben eine 500.000 Zeilen Exceldatei raus. Die wird dann in eine Standardtabelle kopiert (mit Matrixformeln und ähnlichem Speicherfresser), und dann trudeln bei mir die Mails ein warum die Planungstools nicht mehr funktionieren. Die Kollegen sind natürlich lernresistent, was Access oder SQL angeht.
Wie lange lief es denn bei dir durch? Ich hab hier eine exemplarische Monsterdatei, bei der ich den selben Weg gehe (Formeln Zeile für Zeile schreiben).
Die hat einen Button der das Makro auslöst, dazu habe ich ein Hinweisschild eingeblendet auf dem sinngemäß steht "Der Computer arbeitet, geh einen Kaffe trinken und eine rauchen. In Ruhe". Die läuft allerdings "nur" 20 minuten durch, keine 10 Stunden :-)
Grüße,
Klaus M.vdT.
Monster-Dateien-Erleger

Anzeige
AW: Danke für die Rückmeldung! mit Text.
22.02.2013 04:10:47
Arthur
Hallo Klaus
Hehe, ja wie du sagst, es ist auch bei mir eine riesige Liste. Ich hätte von Anfang an besser planen sollen. Aber so hat es jetzt zum Glück auch geklappt.
Es sind sverweise die wieder auf sverweise und indexformel/verlinkungen zurückgreifen + Matrixformeln, mit tausenden von Zeilen aber auch Spalten, also wohl ein paar Millionen Formeln. Bis die ganze liste durchgerackert wurde hat es 22 Stunden gedauert (mit im Excel unten rechts angezeigten 24 Prozessoren, im Büro)!
Aber eben, dass nächste Mal besser planen und die Tabelle wenns geht irgendwie in Mehrere unterteilen, dies habe ich daraus gelernt :-)
Einen schönen Tag und nochmals danke vielmals!
Arthur

Anzeige
Tipp
23.02.2013 00:26:29
Rudi
Hallo,
anstatt mit Matrixformeln besser mit Hifsspalten arbeiten. Ist erheblich schneller.
Genauso die beliebten 'ohne Wenn' durch Multiplikation vermeiden.
z.B.
=(A1="x")*Summewenn(C:C;B1) ist viel langsamer als =Wenn(A1"x";0;Summewenn(C:C;B1)), da immer Summewenn() berechnet wird, anstatt 0 auszugeben, wenn A1"x" ist. Nicht immer ist die kürzere Formel die bessere.
Weitere Kandidaten: Bereich.Verschieben(), Indirekt(), Bed. Formatierungen, .....
Gruß
Rudi

AW: Simples Makro gesucht - Zeile runterziehen(Formel)
19.02.2013 18:28:04
Daniel
HI
wie sehen den die Formeln aus?
kann man da eventuell was optimieren?
mir fallen da folgende Möglichkeiten ein:
- rechenintensive Zwischenergebnisse, welche für mehrere zellen gleich sind, in Hilfsspalten auslagern.
Beispiel: du hast viele SVerweise in einer Zeile, welche sich alle auf die gleiche Zeile aber in unterschiedlichen Spalten beziehen.
Dann ist es sinnvoller, die Zeilennummer des Suchbegriffs in einer Hilfsspalte per VERGLEICH zu ermitteln und dann in den anderne Zellen einfach die INDEX-Funktion zu verwenden.
- oft lassen sich auch durch eine geschickte Sortierung die Rechenzeiten dramatisch reduzieren.
bsp ist es auch wieder beim SVerweisen mit grosser Suchmatrix sinnvoll, diese Aufsteigend zu sortieren, um dann mit dem SVerweis mit 4. Parameter = WAHR arbeiten zu können, welcher bei grossen Datenmengen um welten schneller ist als sein "falscher" Bruder.
Gruß Daniel
Anzeige

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige