Makro - variables Formeln kopieren / Versionsunterschiede?

[MaGleV]

Guten Tag Liebe Community,

ich hätte folgendes Problem bzw. Rückfrage.

Ich habe eine Tabelle mit der befüllten Daten in Spalte A bis G.
Die Spalte A enthält eine variable Anzahl von einkopierten Inhalten, die sich von Zeile 5 bis 10.005 erstrecken können.
In Spalte B bis G befinden sich Formeln, die im ersten Moment von Zeile 1 in Zeile 5 kopiert werden und dann in jede Zeile kopiert werden sollen in denen sich ein Wert in Spalte A befindet.

Derzeit werden in meinen Code pauschal alle 10.000 Zeilen bedient:

Code:

Range("B1:G1").Select
    Selection.Copy
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("B5:G10005"), Type:=xlFillDefault
    Range("B5:G18005").Select

Mit was für einen Formelabschnitt könnte ich nun die Performance des Makros verbessern und muss ich hierbei auf eventuelle Versionsunterschiede von Excel 2016 u. 365 achten?

Vielen Dank für eure Unterstützung.

BG
MaGleV

[Klaus-Dieter]

Hallo,

Formeln im Vorhinein in eine Liste zu schreiben, ist kein guter Ansatz. Es gibt mindestens zwei Möglichkeiten, das zu vermeiden:

1. Wenn Sie neue Zeilen mit Daten am Ende einer Liste hinzufügen, werden von Excel auf die neuen Zeilen konsistente Formatierungen und Formeln übertragen. Damit ein Format erweitert wird, müssen drei von fünf vorangegangenen Zellen dasselbe Format verwenden. Damit eine Formel erweitert wird, müssen alle vorangegangenen Formeln konsistent sein. (Aus der Excelhilfe)

2. du legst deine Liste als "Intelligente Tabelle" an, auch dann tragen sich die Formeln automatisch in jede neue Zeile ein.

[LCohen]

Derzeit werden in meinen Code pauschal alle 10.000 Zeilen bedient:

... und das ist gut so, wie auch Klaus-Dieter schrieb.

Denn Excel verwaltet Blöcke von Gleichartigem viel besser, als wenn es auseinandergerissen ist. Du würdest also eher die Formel um eine entsprechende Behandlung erweitern - und weil so etwas doof ist (!), unterlässt Du einfach die Lücken in den Daten. Und schon sind wir mit der Predigt am Ziel.

[Case]

Hallo MaGleV, :19:

hier mal ein Beispiel (das wäre dann das Wort zum Sonntag): :21:

  Formeln_per_VBA_eintragen.xlsb (Größe: 16,95 KB / Downloads: 6)

Du kannst Formeln auf einen Rutsch in ganze Bereiche schreiben. Ohne das "Wenn..." kannst du auch mit SpecialCells arbeiten.

Oder du hältst dich an die Angaben meiner Vorredner.

[MaGleV]

Erst einmal vielen Dank für die Rückmeldungen.

Mein Problem liegt hauptsächlich in der Performance.

Ich habe die Datei bereits mit weiteren Formeln erweitert und die Möglichen Inhalte der Spalte A im Umfang erweitert.
(Formeln von Spalte B bis ca. AZ / Inhalt Spalte A bis Zeile 20.000)

Des Weiteren ist es gewollt, dass die Datei in Ihrer ursprünglichen Form relativ klein ist, deswegen sollen die Formeln auch erst im Nachgang auf die eingespielten Daten (Spalte A) via Makro angelegt werden.

Nun habe ich bereits jetzt schon das Dilemma, dass auf einem schwachen Rechner mein Makro, den Prozessor schon leicht in die Knie zwingt.
Gibt es hier eventuell die Möglichkeit Excel dazu zu bewegen jede Spalte einzeln "abzuarbeiten" um eine mögliche "Systemüberforderung" zu umgehen?

@Case : Vielen Dank für deine Datei aber meine Excel Künste reichen derzeit nicht aus um dein Makro Befehl so umzuschreiben, dass ich es effektiv für mein Makro nutzen könnte. (Kompilierungsfehler) :/

Code:

Ausschnitt:


Range("B1:G1").Select
    Selection.Copy
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("B5:AZ20005"), Type:=xlFillDefault
    Range("B5:AZ20005").Select

[Case]

Hallo MaGleV, :19:

da hätte ich eine durchaus gewagte Idee - lade doch eine Beispieldatei mit den Formeln und vom Aufbau her dem Original gleich hier hoch. :21:

[MaGleV]

Guten Tag ,

@Case das klingt natüüürlich sehr plausibel. ^^

Anbei der versimpelte Dateiausschnitt.
Wäre super, wenn das Makro wirklich nur die Zeilen bedient, die durch einen anderen Schritt in Spalte A des Reiters Tabelle auch tatsächlich befüllt werden.

[Case]

Hallo, :19:

bezogen auf deine Beispieldatei: :21:

  Formel_per_VBA_aus_Zeile_1_in_Bereich_kopieren.xlsb (Größe: 145,97 KB / Downloads: 4)

"1, paar zerquetschte Sekunden" für 25.000 Zeilen und Spalte B:AZ.

[MaGleV]

Hi Case,

vielen Dank für deine Mühen.
Der folgende Abschnitt ist genau das was ich benötigt habe und lässt sich anscheinend problemlos in meinen Code via Copy Paste übernehmen.

Code:

With Tabelle1
        .Range("B3:AZ" & Tabelle1.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
        .Range("B1:AZ1").Copy .Range("B3:AZ" & Tabelle1.Cells(Rows.Count, 1).End(xlUp).Row)
        Application.CutCopyMode = False
    End With

Bitte helf mir noch ein letztes mal aufs Pferd.
Wieso kann Excel die Bezeichnung "Tabelle1" verwenden? Der Reiter/ das Tabellenblatt heist doch "Tabelle" oder ist diese Bezeichnung abhängig vom Active Sheet?

[Case]

Hallo, :19:

"Tabelle1" ist der CodeNmae eines Tabellenblattes in einem deutschen Excel - in Englisch "Sheet1". Schau mal im VBA Editor im linken Fenster steht bei dir "Tabelle1(Tabelle)" und "Tabelle2(Makroschaltfläche)".

Das vor der Klammer ist der Codename, das in der Klammer der Name in den du die Tabelle umbenennen kannst.

Es ist bequemer und sicherer mit dem Codename zu arbeiten, denn der klappt auch dann noch, wenn du das Tabellenblatt umbenennst, oder verschiebst (falls du mit dem Index arbeitest).

Du könntest auch so schreiben: :21:

Code:

With ThisWorkbook.Worksheets("Tabelle")

Das klappt aber nicht mehr, wenn du umbenennst.