In Excel Formel je Spalte per VBA-Schleife runterziehen

Computer: Software & Programmierung Office Software
#1

Hallo zusammen

Ich bin neu auf dieser Plattform und suche Hilfe zu folgendem Problem. Kenne mich mit Schleifen noch zuwenig aus.

In einem Excel sind je Zellen K9 bis AX9 komplexe Formeln hinterlegt, welche Berechnungen aus Nachbarzellen ausführen. In VBA hab ichs hingebracht, dass zuerst die Formel in K9 heruntergezogen wird bis Zeile 2000, danach die Formel L9 bis Zeile 2000 usw. Da Excel an Grenzen stösst, wandle ich die Formeln ab Zeile 10 bis 2000 nach abgearbeiteten 5 Spalten um in Festwerte. In VBA hab ich mir die Mühe gemacht, nun für diese 40 Spalten das Formelrunterziehen einzeln anzusprechen.

Gibt es keine Möglichkeit, dies viel einfacher und evtl. performanter hinzukriegen? Z.B. Abarbeiten mit Schleife Spalte 1-5, Formeln in Festwerte übernehmen, dann das nächste Paket etc.?

Danke für Eure Hilfe,
Silence

#2

Hallo,

vielleicht verstehe ich ja das Problem nicht, aber der übliche Weg ist sehr einfach: die Zelle(n) anklicken, rechts unten auf die Ecke der so hervor gehobenen Zelle(n) klicken und dann einfach die Maus nach unten ziehen, bis man in Zeile 2000 angekommen ist. Und schon wird die Formel in die Zellen der Zeilen 10 bis 2000 kopiert…

Grüße
Pierre

#3

Danke für Deine Antwort, aber das ist mir schon klar. Ich habe auf diese Weise über ein Makro den VBA-Code erstellt. Den Code steuere ich mit einem Button an. Ich möchte grundsätzlich den Code optimieren, jetzt macht er folgendes:
Abfüllung der Formel in 1. Spalte und Umwandlung dann in Festwerte.
Dann kommt der nächste Code, der dasselbe für die 2. Spalte macht.
Ich habe nun 40 solcher Codes.

Es müsste doch einfacher gehen, so etwas mit Schleifen zu machen. Denn wenn ich beispielsweise eine Spalte einfüge kann es sein, dass ich danach die 40 Codes einzeln von Hand wieder anpassen muss.

Hoffe es ist nun verständlicher,
Gruss Silence

#4

hi,

ach das ist quasi selbsterklärend.

schau mal da: https://www.excel-easy.com/vba/loop.html

einfach ein

For i=1 to 2000

Zeile i+1 mit der Funktion füllen.
Zeile i in Festwert umwandeln.

next i

ich würde je eine Zeile machen, das ist übersichtlicher.

Wenn du Probleme hast, das i in deinen code rein zu basteln, poste mal eine Zeile davon.

grüße
lipi

#5

Wenn die Problematik richtig verstanden habe, benötigst Du gar keine Schleife.

Formel „runterziehen“:

Application.Sheets(1).Range("K9:AX2000").Formula = Application.Sheets(1).Range("K9:AX9").Formula

Formel durch berechneten Wert ersetzen:

Application.Sheets(1).Range("K10:AX2000").Value = Application.Sheets(1).Range("K10:AX2000").Value

Kann sein, dass Du nach dem Formel runterziehen Excel ein bisschen Zeit zum Rechnen lassen muss. Dann folgende Codezeile einfügen:

Application.Sheets(1).Calculate

Allerdings halte ich nicht viel davon, aus Performance-Gründen Formeln mit Festwerten zu ersetzen, wenn die Tabelle über die Einmalverwendung hinausgeht. Formel lassen sich meistens noch optimieren: https://docs.microsoft.com/de-de/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions

#7

Danke Dir vielmals lipi, ich werde das mal anschauen :+1:
Grüsse Silence

#8

Hallo Lfm001, Danke für deine Hilfe. Wenn ich das in ein neues Excel einbaue mit Beispieldaten, funktioniert das wunderprächtig. Bau ich das aber in mein Excel ein, mit genau derselben Formel und Zellbezügen, zieht er mir die Formeln nicht runter. Ich probier noch bisschen rum, vielleicht find ich raus warum er es im einen Excel macht, im andern aber nicht.

Zur Umwandlung von Festwerten: Das Excel bezieht Daten von einem SQL-Server. Von dort werden bestimmte Daten rausgezogen und dann in einem neuen Tabellenblatt weiterverarbeitet. Ich hatte anfänglich keine Umwandlung zu Festwerten drin, aber Excel hängt irgendwann wenn ich alle Formeln so drin lasse, und rechnet ständig wieder nach. Formelberechnung deaktivieren macht keinen Sinn, da andere Benutzer damit auch arbeiten.

Grüsse Silence

#9

Hatte einen falschen Bezug zum Tabellenblatt. In meinem Excel funktioniert das nicht mit dem Tabellenbezug, sondern ich muss explizit den verwendeten Namen angeben:
Application.Sheets(„TEST“).Range(„K9:K2000“).Formula = Application.Sheets(„TEST“).Range(„K9:K9“).Formula

Neues Problem: Die Formel ändert sich merkwürdigerweise nach Ausführung des Codes und funktioniert nicht mehr. Meine funktionierende Formel:
=WENNFEHLER(WENN($B9<>"";INDEX(INDIREKT(„TabelleXY!$C$10:$C$1000“);(VERGLEICH(($B9&$C9)&K$8;INDIREKT(„TabelleXY!$E$10:$E$1000“)&INDIREKT(„TabelleXY!$A$10:$A$1000“);0)));"");"")

Nach Ausführung des Codes ändert sich die Formel, vor dem „INDIREKT“ wird das @-Zeichen eingesetzt:
=WENNFEHLER(WENN($B9<>"";INDEX(@INDIREKT(„TabelleXY!$C$10:$C$1000“);(VERGLEICH(($B9&$C9)&K$8;INDIREKT(„TabelleXY!$E$10:$E$1000“)&@INDIREKT(„TabelleXY!$A$10:$A$1000“);0)));"");"")

Zieh ich die Formel von Hand runter oder mit meinem alten Coding, ändert sich die Formel nicht :upside_down_face:

#10

Schuss ins Blaue: Versuche es mal mit .Formula2 statt .Formula

Ansonsten kann man via VBA auch den „Umweg“ über die Zwischenablage gehen und so das Verhalten beim händischen Kopieren 1:1 simulieren.

Beim Vergleich zum händischen Kopieren bedenke, dass im VBA-Code nur die Formel kopiert wird.

#11

Genial, mit Formula2 rennt das Ding! :+1:
Herzlichen Dank für die Hilfe und Grüsse aus der Schweiz
Silence