Microsoft Excel

Herbers Excel/VBA-Archiv

Laufzeitfehler in VBA Formel

Betrifft: Laufzeitfehler in VBA Formel von: Vigo
Geschrieben am: 27.07.2014 23:13:14

Hallo zusammen!

Ich sitze schon länger hier und versuche diesen Fehler zu lösen. Bisher leider ohne Erfolg (Laufzeitfehler 1004: anwendungs- oder objektdefinierter Fehler)

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)"
Weiß hier jemand vielleicht weiter??

Viele Grüße, Vigo

  

Betrifft: AW: Laufzeitfehler in VBA Formel von: Oberschlumpf
Geschrieben am: 27.07.2014 23:29:31

Hi

Vielleicht zu viele Zeichen in einer Codezeile?

Versuch doch mal, die Codezeile von klein nach groß aufzubauen.

Erst ausprobieren, ob

Range("B3").FormulaR1C1 = "=ROUNDUP((SUMIFS(Datenzusammenführung!R2C11:R"

funktioniert.
Wenn ja, dann Formel erweitern...so lange, bis Fehler auftritt.
Wenn Fehler schon im ersten Versuchsteil, dann da den Fehler suchen und nicht in der ewig langen Zeile.

Hilfts?

Ciao
Thorsten


  

Betrifft: AW: Laufzeitfehler in VBA Formel von: Daniel
Geschrieben am: 27.07.2014 23:42:52

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


  

Betrifft: AW: Laufzeitfehler in VBA Formel von: Daniel
Geschrieben am: 28.07.2014 00:01:05

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


  

Betrifft: AW: Laufzeitfehler in VBA Formel von: Crazy Tom
Geschrieben am: 28.07.2014 00:08:37

Hallo zusammen

nur zur Info
http://www.ms-office-forum.net/forum/showthread.php?t=312119

MfG Tom


  

Betrifft: AW: Laufzeitfehler in VBA Formel von: Daniel
Geschrieben am: 28.07.2014 00:25:48

Danke für die Info
was ich nicht so ganz verstehe, warum er hier die Frage nochmal stellt, obwohl er zu der Uhrzeit im anderen Forum das Problem schon gelöst war!

vielleicht sollte man doch mal aufhören, die Hilfe in Foren kostenlos anzubieten.

Gruß Daniel


 

Beiträge aus den Excel-Beispielen zum Thema "Laufzeitfehler in VBA Formel"