EXCEL VBA: Formel berechnen und Ergebnis als Wert in Zelle schreiben

Zoltan67 · 17. Mai 2019

hallo,

ich muss in einer Excel-Datei sehr viele Berechnungen durchführen, was sehr viel Berechnungszeit braucht. Ich möchte deshalb die Formeln aus der Datei entfernen, in dem ein Makro die Formel berechnen und das Ergebnis als Wert in die jeweilige Zelle schreiben soll. Leider reichen meine VBA-Kenntnisse dazu nicht aus...

Das Blatt ist so aufgebaut, dass zuerst alle Werte in einer Zeile berechnet werden müssten (von links nach rechts gehend) und dann kommt dasselbe in der nächsten Zeile (besser gesagt 15 Zeilen darunter) dran. Und das bis zur letzten Berechnung in der Zeile 3523. (Ich denke, sowohl Zeilen- als auch Spaltennummern müssten durch Variablen definiert sein - auf die Art cells(i,j) - und die Zellen würden durch eine verschachtelte for/next Schleife für i und j angesteuert.)

Die Formel ist recht umfangreich (wobei manche Werte aus einem anderen Blatt ("xru") kommen, der Rest ist im Berechnungsblatt):

z.B. in Zelle L13 (da müsste begonnen werden) =SUMME((L3*L8/L6*xru!L$2-K3*K8/K6*xru!K$2)/MITTELWERT(xru!K$2,xru!L$2),(L3*L9/L6*xru!L$3-K3*K9/K6*xru!K$3)/MITTELWERT(xru!K$3,xru!L$3),(L3*L10/L6*xru!L$4-K3*K10/K6*xru!K$4)/MITTELWERT(xru!K$4,xru!L$4),(L3*L11/L6*xru!L$5-K3*K11/K6*xru!K$5)/MITTELWERT(xru!K$5,xru!L$5),(L3*(L6-L8-L9-L10-L11)/L6-K3*(K6-K8-K9-K10-K11)/K6))+(L4*L12-K4*K12)/MITTELWERT(K12,L12)+(L5-K5)

dann als nächstes (alles um eine Spalte versetzt):

in Zelle M13 =SUMME((M3*M8/M6*xru!M$2-L3*L8/L6*xru!L$2)/MITTELWERT(xru!L$2,xru!M$2),(M3*M9/M6*xru!M$3-L3*L9/L6*xru!L$3)/MITTELWERT(xru!L$3,xru!M$3),(M3*M10/M6*xru!M$4-L3*L10/L6*xru!L$4)/MITTELWERT(xru!L$4,xru!M$4),(M3*M11/M6*xru!M$5-L3*L11/L6*xru!L$5)/MITTELWERT(xru!L$5,xru!M$5),(M3*(M6-M8-M9-M10-M11)/M6-L3*(L6-L8-L9-L10-L11)/L6))+(M4*M12-L4*L12)/MITTELWERT(L12,M12)+(M5-L5)

und so weiter in Zeile 13 bis zur letzten Spalte (die letzte Spalte kann ich definieren).

Dann weiter in der nächsten Zeile 15 Zeilen drunter:

in Zelle L28 =SUMME((L18*L23/L21*xru!L$2-K18*K23/K21*xru!K$2)/MITTELWERT(xru!K$2,xru!L$2),(L18*L24/L21*xru!L$3-K18*K24/K21*xru!K$3)/MITTELWERT(xru!K$3,xru!L$3),(L18*L25/L21*xru!L$4-K18*K25/K21*xru!K$4)/MITTELWERT(xru!K$4,xru!L$4),(L18*L26/L21*xru!L$5-K18*K26/K21*xru!K$5)/MITTELWERT(xru!K$5,xru!L$5),(L18*(L21-L23-L24-L25-L26)/L21-K18*(K21-K23-K24-K25-K26)/K21))+(L19*L27-K19*K27)/MITTELWERT(K27,L27)+(L20-K20)

dann wieder Spalte M, und so weiter bis Zeile 3523.

Könnte mir jemand helfen?

(Die For/Next Schleife würde ich noch zusammen bringen, aber ich habe keine Ahnung, wie ich die Formel im Makro in der cells(i,j) Schreibweise darstelle...)

Zoltan67

Lieber Claus,

das hat auch funktioniert.

Dann habe ich das Makro aber ergänzt, damit er das Ganze in mehreren Sheets ausführt aber da dürfte ich diese Schleife (fett markiert) falsch platziert haben... (sheet "output sheets" enthält
in Spalte A die Namen der Sheets, die durchgegangen werden sollten.)

Sub calculation()

'

' calculation of exchange rate adjusted changes

'

'

Dim Spalten As Integer, i As Integer

Dim rngBig As Range

With Application

.ScreenUpdating = False

.EnableEvents = False

.calculation = xlCalculationManual

End With

On Error GoTo CleanUp

Sheets("Output Sheets").Select

Range("A1").Select

Selection.End(xlDown).Select

LZo = Selection.Row

For Each b In Worksheets("Output Sheets").Range(Cells(1, 1), Cells(LZo, 1))

Landsheet = b.Value

Sheets(Landsheet).Select

With ActiveSheet

'Hier die Spalten f?r die Formeln anpassen

Spalten = .Columns("CI").Column - .Columns("L").Column + 1

For i = 13 To 3523 Step 15

If rngBig Is Nothing Then

Set rngBig = .Cells(i, "L").Resize(1, Spalten)

Else

Set rngBig = Union(rngBig, .Cells(i, "L").Resize(1, Spalten))

End If

Next

With rngBig

.Formula = "=IFERROR(SUM((L3*L8/L6*xru!L$2-K3*K8/K6*xru!K$2)/Average(xru!K$2,xru!L$2)," _

& "(L3*L9/L6*xru!L$3-K3*K9/K6*xru!K$3)/Average(xru!K$3,xru!L$3)," _

& "(L3*L10/L6*xru!L$4-K3*K10/K6*xru!K$4)/Average(xru!K$4,xru!L$4)," _

& "(L3*L11/L6*xru!L$5-K3*K11/K6*xru!K$5)/Average(xru!K$5,xru!L$5)," _

& "(L3*(L6-L8-L9-L10-L11)/L6-K3*(K6-K8-K9-K10-K11)/K6))+(L4*L12-K4*K12)/Average(K12,L12)+(L5-K5),L2-K2)"

.Calculate

For Each myRow In rngBig

With myRow

.Value = .Value

End With

Next myRow

End With

End With

Next b

CleanUp:

With Application

.calculation = xlCalculationAutomatic

.EnableEvents = True

.ScreenUpdating = True

End With

End Sub

Claus Busch

Hallo Zoltan,

dann ändere den Teil der mit "With rngBig" beginnt so ab:

With rngBig

        .Formula = "=IFERROR(SUM((L3*L8/L6*xru!L$2-K3*K8/K6*xru!K$2)/Average(xru!K$2,xru!L$2)," _

            & "(L3*L9/L6*xru!L$3-K3*K9/K6*xru!K$3)/Average(xru!K$3,xru!L$3)," _

            & "(L3*L10/L6*xru!L$4-K3*K10/K6*xru!K$4)/Average(xru!K$4,xru!L$4)," _

            & "(L3*L11/L6*xru!L$5-K3*K11/K6*xru!K$5)/Average(xru!K$5,xru!L$5)," _

            & "(L3*(L6-L8-L9-L10-L11)/L6-K3*(K6-K8-K9-K10-K11))/K6+(L4*L12-K4*K12)/Average(K12,L12)" _

            & "+(L5-K5)),0)"

        .Calculate

        For Each myRow In rngBig

            With myRow

                .Value = .Value

            End With

        Next

End With

Die Formel einzuschreiben, dann nur den Bereich mit den Formeln berechnen zu lassen und die Ergebnisse in Werte zu wandeln, ist meines Erachtens der schnellste Weg.

Claus

Claus Busch

Hallo Zoltan,

wo fehlen die Werte? In Blatt "xru" oder in dem Blatt mit den Formeln? Fehlt dann der ganze Block, also z.B. alle Werte in L3:L12 oder nur einzelne Zahlen?

Claus

Claus Busch

Hallo Zoltan,

probiere es mal so (Spalten musst du noch anpassen):

Sub Berechnung()

Dim Summand1 As Double, Summand2 As Double, Summand3 As Double

Dim Summand4 As Double, Summand5 As Double

Dim n As Integer, i As IntegerWith Application

    For i = 13 To 3523 Step 15

        For n = Columns("L").Column To Columns("S").Column

            Summand1 = (Cells(i - 10, n) * Cells(i - 5, n) / Cells(i - 7, n) * _

                Sheets("xru").Cells(2, n) - Cells(i - 10, n - 1) * Cells(i - 5, n - 1) _

                / Cells(i - 7, n - 1) * Sheets("xru").Cells(2, n - 1)) / _

                .Average(Sheets("xru").Cells(2, n - 1), Sheets("xru").Cells(2, n))

                

            Summand2 = (Cells(i - 10, n) * Cells(i - 4, n) / Cells(i - 4, n) * _

                Sheets("xru").Cells(3, n) - Cells(i - 10, n - 1) * Cells(i - 4, n - 1) / _

                Cells(i - 7, n - 1) * Sheets("xru").Cells(i - 10, n - 1)) / _

                .Average(Sheets("xru").Cells(i - 10, n - 1), Sheets("xru").Cells(i - 10, n))

                

            Summand3 = (Cells(i - 10, n) * Cells(i - 3, n) / Cells(i - 7, n) * _

                Sheets("xru").Cells(4, n) - Cells(i - 10, n - 1) * Cells(i - 3, n - 1) _

                / Cells(i - 7, n - 1) * Sheets("xru").Cells(4, n - 1)) / _

                .Average(Sheets("xru").Cells(4, n - 1), Sheets("xru").Cells(4, n))

                

            Summand4 = (.Cells(i - 10, n) * Cells(i - 2, n) / Cells(i - 7, n) * _

                Sheets("xru").Cells(5, n) - Cells(i - 10, n - 1) * Cells(i - 2, n - 1) / _

                Cells(i - 7, n - 1) * Sheets("xru").Cells(5, n - 1)) / _

                .Average(Sheets("xru").Cells(5, n - 1), Sheets("xru").Cells(5, n))

                

            Summand5 = (Cells(i - 10, n) * (Cells(i - 7, n) - Cells(i - 5, n) - Cells(i - 4, n) - _

                Cells(i - 3, n) - Cells(i - 2, n)) / (Cells(i - 7, n) - Cells(i - 10, n - 1) * _

                (Cells(i - 7, n - 1) - Cells(i - 5, n - 1) - Cells(i - 4, n - 1) - Cells(i - 3, n - 1) _

                - Cells(i - 2, n - 1)) / Cells(i - 7, n - 1)) / Cells(i - 7, n - 1)) + _

                (Cells(i - 9, n) * Cells(i - 1, n) - Cells(i - 9, n - 1) * Cells(i - 1, n - 1)) _

                / .Average(Sheets("xru").Cells(i - 1, n - 1), Sheets("xru").Cells(i - 1, n)) _

                + (Cells(i - 8, n) - Cells(i - 8, n - 1))

            

            Cells(i, n) = Summand1 + Summand2 + Summand3 + Summand4 + Summand5

        Next

    Next

End With                

End Sub

Claus

Zoltan67

Lieber Claus,

vielen herzlichen Dank für die Hinweise und die Lösung.

Jetzt funktioniert es super und macht mein Leben leichter.

Wünsche dir noch einen schönen Tag.

LG, Zoltan

Hi,

willkommen im Windows Forum!

EXCEL VBA: Formel berechnen und Ergebnis als Wert in Zelle schreiben - Similar Threads - EXCEL VBA Formel	Forum	Datum
Ergebnis einer Formel in ander Zelle anzeigen Ergebnis einer Formel in ander Zelle anzeigen: Hallo Zusammen,bis zur version 2019 konnte ich das Ergebnis einer Gleichung z.B. 12+85*5-15/3 in der benachbarten Zelle über =AUSWERTENINDIREKT"ZS-1";0 anzeigen lassen, in der Version 2021...	Microsoft Office	12. März 2024
Excel: Formel und Ergebnis wird in weiterer Zelle grau dargestellt Excel: Formel und Ergebnis wird in weiterer Zelle grau dargestellt: Liebe Community,nachdem ich eine lange Formel in eine Excel Zelle eingetragen habe tauchte ein Fenster auf, dass ich leider nicht gelesen habe.Das Ergebnis davon ist, dass ich jetzt ein Problem...	Microsoft Office	3. Dezember 2021
Formel in Zellen schreiben Formel in Zellen schreiben: Hallo - habe schon mal was zu diesem Thema wissen wollen. Nun ist Zeit vergangen und bei einem anderen Projekt will es nicht klappen. Möchte eine Formel in einen Zellenbereich schreiben Dim...	Microsoft Office	20. März 2021
Excel VBA – Mit CommandButton1_Click einen Wert in eine Zelle schreiben Excel VBA – Mit CommandButton1_Click einen Wert in eine Zelle schreiben: Ich habe einen Datenbereich von A4:V10550. Wie kann ich in die Zelle A2 einen Wert (z.B 1) schreiben, wenn ich CommandButton1_Click() anklicke und beim Autofilter in der Spalte A4:A10550 Werte...	Microsoft Office	20. Februar 2019
Ergebnis der Formel erst, wenn alle Zellen eines Bereichs Wert haben Ergebnis der Formel erst, wenn alle Zellen eines Bereichs Wert haben: Hallo zusammen, ich verzweifle gerade an einer Aufgabe, und zwar will ich dass der Mittelwert von einem definierten Spaltenbereich erst dann berechnet wird, wenn auch alle Prozentangaben...	Microsoft Office	11. Januar 2019
WENN-Formel mit Ergebnis einer vorhergehenden Formel in derselben Zelle WENN-Formel mit Ergebnis einer vorhergehenden Formel in derselben Zelle: Hallo, ich hoffe ihr könnt mir weiterhelfen. Ich habe mich mit obigem Problem ziemlich festgefahren und hatte auch nach intensiverer Suche bei Onkel Google kein Glück. Zur Ausgangssituation:...	Microsoft Office	6. Juli 2017
VBA Makro - Formel in Zelle schreiben VBA Makro - Formel in Zelle schreiben: Zum Abschluss meines Sub Fahrzeuge () möchte ich die Formel {=MITTELWERT(WENN(P2:P156="";R2:R156))} der Zelle R1 im Tabellenblatt "Gebraucht" zuweisen. Habe schon so einiges probiert - aber...	Microsoft Office	3. Februar 2017

EXCEL VBA: Formel berechnen und Ergebnis als Wert in Zelle schreiben