Hallo community,

ich erzeuge in einer Excel-Tabelle per VBA-Makros neue Tabellenblätter. Dabei soll in einer bestimmten Zelle die unten abgebildete Formel eingesetzt werden:

=SUMME(D22-E22)+((ZÄHLENWENN(Jan!$D$6:$AH$6;Parameter!B45))*Sollstunden)

Ich habe es mit folgendem Ansatz probiert, erhalte aber bei der letzten abgebildeten Zeile den Laufzeitfehler '1004':
monat = Application.InputBox("Übersicht für welchen Monat (1-12)", , , , , , , 1)
monatkurz = MonthName(monat, True)
zahl = monat + 21
Formel = "=SUMME(D" + CStr(zahl) + "-E" + CStr(zahl) + ")+((ZÄHLENWENN(" + monatkurz + "!$D$6:$AH$6;Parameter!B45))*Sollstunden)"
Worksheets(V).Cells(zahl, 6).Formula = formel

Ich denke, der Code kommt wegen der Kommata ins Schleudern, aber vielleicht gibt es ja einen anderen Weg zum Ziel?

Kann mir hier jemand auf die Sprünge helfen?

Statt Worksheets musst du Sheets schreiben.

Außerdem:

Wenn man mit VBA Formeln in Zellen "schreiben" will, muss man die englischen Formelbegriffe verwenden und statt Semikolon Komma verwenden. Außerdem müssen die Bezüge nicht im "A1"-Format, sondern im "Z1S1"-Format (englisch: "R1C1") angegeben werden. (zur Erklärung: Z=Zeile, S=Spalte, R=Row, C=Column)

Da ich die englischen Begriffe nicht auswendig kenne und auch nicht weiß, wo man sie nachschlagen kann, mache ich das immer so, dass ich aufzeichne, wie ich die Formel in eine Zelle eingebe und dann den Code des aufgezeichneten Makros in meinen Code reinkopiere. In deinem Fall, also

=SUMME(D22-E22)+((ZÄHLENWENN(Jan!$D$6:$AH$6;Parameter!B45))*Sollstunden)

sähe der Code so aus (wenn ich es in A1 eingebe):




Wenn nach R bzw. C eine Zahl in eckigen Klammern steht, ist es ein relativer Bezug. R[21] bedeutet dann 21 Zeilen weiter unten, C[3] 3 Spalten weiter rechts. Steht daneben eine Zahl ohne eckige Klammer, ist es ein absoulter Bezug. R6 bedeutet Zeile 6, C4 Spalte 4 (also Spalte D), R6C4 also $D$6. Definierte Namen und Tabellennamen bleiben wie du siehst unverändert.

Statt "ActiveCell" kann natürlich jeder beliebige Zellverweis stehen, also auch Sheets(V).Cells(zahl, 6). Bei relativen Bezügen musst du natürlich aufpassen.

EDIT: Habe VB-Tag durch den allg. Code-Tag ersetzt, da der VB-Tag einen Fehler verursacht hat.

Warum stellst dus nicht ins VBA-Unterforum?
Du weißt ja offensichtlich, dass es sich hier um VBA handelt...

*verschoben

@mad andy: Sorry, beim nächsten Mal besser...

@roddy: danke für die Antwort, der Trick mit der englischen Schreibweise war es!

Zur Info: Nur das Tauschen von "Sheets" anstelle von "Worksheets" brachte nichts, der Laufzeitfehler kommt trotzdem. Und eine Makro-Aufzeichnung hilft auch nichts, da die Bezüge in Abhängigkeit zum neuen Blatt wechseln (daher die Variablen monat, monatkurz und zahl).

Das mit dem Aufzeichnen mache ich ja im Grunde nur, um den englischen Begriff für die Funktion herauszufinden.