Laufzeitfehler in VBA Formel

Range("B3").FormulaR1C1 = _ "=ROUNDUP((SUMIFS(Datenzusammenführung!R2C11:R" & letzte & "C11,Datenzusammenführung!R2C7:R" _ & letzte & "C7,""STRGB"",Datenzusammenführung!R2C14:R" & letzte & "C14,1)/1),0)" _ + "ROUNDUP((SUMIFS(Datenzusammenführung!R2C11:R" & letzte & "C11,Datenzusammenführung!R2C7: _ R" & letzte & "C7,""STRGB"",Datenzusammenführung!R2C14:R" & letzte & "C14,2)/2),0)" _ + "ROUNDUP((SUMIFS(Datenzusammenführung!R2C11:R" & letzte & "C11,Datenzusammenführung!R2C7: _ R" & letzte & "C7,""STRGB"",Datenzusammenführung!R2C14:R" & letzte & "C14,3)/3),0)" _ + "ROUNDUP((SUMIFS(Datenzusammenführung!R2C11:R" & letzte & "C11,Datenzusammenführung!R2C7: _ R" & letzte & "C7,""STRGB"",Datenzusammenführung!R2C14:R" & letzte & "C14,4)/4),0)" _ + "ROUNDUP((SUMIFS(Datenzusammenführung!R2C11:R" & letzte & "C11,Datenzusammenführung!R2C7: _ R" & letzte & "C7,""STRGB"",Datenzusammenführung!R2C14:R" & letzte & "C14,5)/5),0)" _ + "ROUNDUP((SUMIFS(Datenzusammenführung!R2C11:R" & letzte & "C11,Datenzusammenführung!R2C7:R" & letzte & "C7,""STRGB"",Datenzusammenführung!R2C14:R" & letzte & "C14,6)/6),0)"

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

es ist nicht besonders spassig, so Monstereformeln auf Fehler zu durchsuchen.
ich mache das bei so Formeln so, das ich die erstmal von Hand in die Zelle schreibe und mir dann im Direktfenster mit:

?Replace(Selection.FormulaR1C1, """", """""")

die Formel anzeigen lasse (gleich mit gedoppelten Anführungszeichen)
dann kopiere ich die Formel von dort in den VBA-Code.
das Problem mit der letzen Zeile löse ich so:
1. in der Zelle füge ich die Formel bis Zeile 999 ein.
2. im VBA-Code schreibe ich den Formeltext zunächst so in eine Stringvariable und ersetze dann die fiktive Zeilennummer mit der Replace-Funktion durch die echte:

dim Formel as String
dim letze as Long
letze = ...
Formel = "=Sum(R1C1:R999C1)"
Formel = Replace(Formel, "R999C", "R" & letzte & "C")
Range("B1").FormulaR1C1 = Formel

mit dieser vorgehensweise hat man auch solche Monsterformeln ganz gut im Griff.
auch bei Langen Formeln ist der Umweg über die Variable hilfreich, weil man dann die Formel in mehrern Schritten zusammensetzen kann und nicht eine lange Zeile mit dem Unterstrich umbrechen muss.
Bedenke aber, dass es in VBA auch bestimmte Grenzen gibt, was die Länge der Formel angeht, die von VBA in die Zelle geschrieben werden kann, und das ist weniger, als die Formel lang sein darf, wenn man sie von Hand in die Zellen schreibt.
das solltest du mal abchecken.
hierbei gibt es folgende Hilfen:
wenn die Formellänge nur knapp über dieser Grenze liegt, könntest du das Blatt "Datenzuammenführung" vor einfügen der Formel in "X" umbennenen und in der Formel dann nur das "X" als Dateinamen verwenden.
Nach einfügen der Formel kannst du das Blatt wieder umbenennen, die Formel in der Zelle passt sich automatisch an.
sollte der Trick nicht ausreichen, dann hilft bei langen Formeln folgender Workaround:
1. lasse die Formel mit "xxx=" beginnen. Sie ist dann nur normaler Text und Texte können in wesentlich grösserer Länge in die Zelle geschrieben werden als Formeln
2. Wenn du die Formel in die Zelle geschrieben hasst, ersetze mit der Ersetzen-Funktion das "xxx=" durch "=". Excel erkennt dann, dass es eine Formel sein soll und erstellt diese korrekt
Damit das funktioniert, muss die Formel in der .Formula-Schreibweise geschreiben sein (englisch mit A1-Zellbezügen)
Gruß Daniel

Ein Fehler ist auf jeden Fall das +, mit welchem du die verschiedenen SumIfs zusammenaddieren willst.
so wie du es geschrieben hast, ist das Verkettungszeichen für Teiltexte, welches nach dem Zeilenumbruch mit _ notwendig ist.
Damit ist es aber nicht bestandteil der Formel und die Formel die du erzeugst ist falsch, was dann zu dem Fehler führt.
ein Code sieht gekürzt so aus:

"=ROUNDUP((SUMIFS()/1),0)" _
+ "ROUNDUP((SUMIFS()/2),0)"

als Formel dann "=RoundUp(SumIfs(),0)RoundUp((SumIfs(),0)" ergibt, was natürlich falsch ist.
wahrscheinlich müsste der Code so aussehen, wenn du die einzelnen RoundUps zusammenzählen willst:

"=ROUNDUP((SUMIFS()/1),0)+" _
& "ROUNDUP((SUMIFS()/2),0)"

die Klammer um das SumIfs()/x ist nicht notwendig.
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

299 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Laufzeitfehler in VBA Formel

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

299 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread