Beschleunigung meines Skripts

Bild

Betrifft: Beschleunigung meines Skripts
von: henrik r
Geschrieben am: 23.04.2015 14:27:28

Hi zusammen,
ich habe ein VBA Skript, welches in die aktive Zelle eine Formel schreibt, die sich auf die Zelle links daneben bezieht. Es geht dann von der aktiven Zelle immer eine Zelle nach unten und wiederholt diesen Prozess. Das macht es solange, solange die linke Zelle gefüllt ist.

Sub TextformelEinfuegen()
Dim j As Double
ActiveCell.EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Select
Do While ActiveCell.Offset(0, -1).Value <> Empty
j = ActiveCell.Row
ActiveCell.FormulaLocal = "=Text(" & (Chr(ActiveCell.Offset(0, -1).Column + 64)) & j & ";""0"")" _
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.EntireColumn.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
Das Skript funktioniert gut. Es wird jedoch wenn ich viele gefüllte Zeile langsam, da es Zeile für Zeile prüft ob die linke Zelle gefüllt ist und die Formel einfügt.
Gibt es eine Möglichkeit das zu beschleunigen?
Danke und Gruß

Bild

Betrifft: AW: Beschleunigung meines Skripts
von: bst
Geschrieben am: 23.04.2015 14:39:16
Hi,
schreibe die Formel auf einmal in alle Zellen, und nimm dann .Formula = .Value um die Formeln aller Zellen durch ihren Wert zu ersetzen. Dann brauchst Du überhaupt keine Schleife.
Versuche das mal so ähnlich.
cu, Bernd
--

Option Explicit
Sub TextformelEinfuegen()
   Dim j As Long
   ' Die letzte beschriebene Zeile aus der Spalte links von ActiveCell
   j = Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row
   ActiveCell.EntireColumn.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   With Range(ActiveCell, Cells(j, ActiveCell.Column))
      .FormulaR1C1 = "=TEXT(RC[-1],""0"")"
      .Formula = .Value
   End With
End Sub


Bild

Betrifft: AW: Beschleunigung meines Skripts
von: henrik r
Geschrieben am: 23.04.2015 15:18:34
Läuft, besten Dank!

Bild

Betrifft: AW: Beschleunigung meines Skripts
von: henrik
Geschrieben am: 23.04.2015 17:40:09
Wobei eine Frage hätte ich noch. Warum bzw wie funktkioniert denn (Rows.Count) in der Cells Funktion?
Ich kenne Cells nur so, dass man damit Excel quasi die Koordinaten einer Zelle mitteilt, um von dort etwas zu machen. Hier ist ja aber die Count Funktion mit eingebaut...

Bild

Betrifft: AW: Beschleunigung meines Skripts
von: Daniel
Geschrieben am: 23.04.2015 18:16:45
Hi
Rows.Count ergibt die Anzahl der Zeilen und dieser Wert ist gleich der Zeilenkoordinate der untersten Zelle einer Spalte.
Gruß Daniel

Bild

Betrifft: Besser erklärt :-)
von: Klaus M.vdT.
Geschrieben am: 24.04.2015 07:58:01
Hallo Henrik,
Sagen wir mal, du befindest dich in Zelle C7 (activecell). Die letzte beschriebene Zeile ist C30.
j = Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row
Die Spalte (column) von C7 ist Spalte 3. Es löst also auf:
j = Cells(Rows.Count, 3- 1).End(xlUp).Row
bzw gleich ausgerechnet
j = Cells(Rows.Count, 2).End(xlUp).Row
rows.count zählt die Anzahl der Zeilen. Je nach Excelversion, bei dir sind es warscheinlich 1048576 Zeilen.
j = Cells(Rows.Count, 2).End(xlUp).Row
löst also auf nach
j = Cells(1048576, 2).End(xlUp).Row
und das ist identisch mit
j = Range("B1048576").end(xlUp).Row
jetzt passiert die Magie. Geh mal in ein leeres Blatt, schreibe ein "x" in Zelle B30 und wähle dann mit STRG+G (geheZu) die ZElle B1048576 aus. Drücke jetzt die Tastenkombination STRG+UP (Cursor hoch). Wo landest du? Richtig, in B30! Weil STRG+UP nach oben springt, bis etwas gefüllt ist. Die Tastenkombination STRG+UP ist in VBA das gleiche wie "end(xlup).
j = Range("B1048576").end(xlUp).Row
löst demnach auf nach
j = Range("B30").Row
Und der Rest ist einfach. Die Zeile von Zelle B30 ist Zeile 30. Daraus folgt:
j = 30
und du hast die letzte beschriebene Zeile.
Nochmal zum Verständniss: Statt "rows.count" könntest du auch einfach ein sehr große Zahl schreiben.
j = Range("B10000").end(xlUp).Row
würde zu 99% genauso gut funktionieren - aber ins leere greifen, falls und nur falls die Liste jemals über zehntausend Zeilen lang wird.
Ebensogut könnte man direkt die letzte Zeile hinschreiben:
j = Range("B1048576").end(xlUp).Row
Aber was machst du dann, wenn es jemand mit Excel-Version 2003 nutzt? Der hat nur 65554 (?) Zeilen zur Verfügung - gibt einen Fehler. Ebensogut könnte es sein, dass Excel Version 2019 erscheint und zehn Million Zeilen bietet.
Um diese Eventualitäten auszuschließen, benutzt man "rows.count", das ist sauber.
Grüße,
Klaus M.vdT.
Wenn man ganz penibel ist müsste man noch prüfen, ob die letzte Zeile gefüllt ist oder nicht - aber das halte ich für so unwahrscheinlich, das es eher von akademischem als praktischem Belang ist.

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Zeilen per VBA einfügen, bestimmte Werte kopieren!"