Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Makro - variables Formeln kopieren / Versionsunterschiede?
#1
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
Antwortento top
#2
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.
Viele Grüße
Klaus-Dieter
Der Erfolg hat viele Väter, 
der Misserfolg ist ein Waisenkind
Richard Cobden
Antwortento top
#3
(03.10.2020, 17:05)MaGleV schrieb: 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.
Antwortento top
#4
Hallo MaGleV, 19

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

.xlsb   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. Dodgy

Oder du hältst dich an die Angaben meiner Vorredner.
________
Servus
Case
Antwortento top
#5
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
Antwortento top
#6
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
________
Servus
Case
Antwortento top
#7
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.


Angehängte Dateien
.xlsm   Mappe1.xlsm (Größe: 233,48 KB / Downloads: 1)
Antwortento top
#8
Hallo, 19

bezogen auf deine Beispieldatei: 21

.xlsb   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.
________
Servus
Case
[-] Folgende(r) 1 Benutzer sagt Danke an Case für diesen Beitrag:
  • MaGleV
Antwortento top
#9
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?
Antwortento top
#10
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.
________
Servus
Case
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste