VBA Formel Zeilenumbrüche

Hallo Liebe VBA Experts,
ich bekomme unten stehende Formel leider nicht korrekt mit den Zeilenumbrüchen getrennt. Egal ob ich & _ oder nur _ mit und ohne Anführungszeichen etc erhalten ich immer Fehlermeldungen. Erwarte Anweisungsende oder Sprungmarke etc
Wer kann helfen ?
Danke!!!!!
=IF(AND(RC[-29]0,RC[-26]=0,RC[-76]="HAMBK",LEFT(RC[-16],16)"ZWISCHENRECHNUNG") ,CONCATENATE("Storage Days"," ","="," ",RC[-97]," ","/"," ","Free Time"," ","="," ",RC[-33]," ","Day(s) "," ","/"," ",RC[-32]," ","Day(s) "," ","@",RC[-29]/RC[-30]," ","per Day"," ","/"," ",R1C[-92]," ",RC[-92]," ","/"," ",R1C[-90]," ",RC[-90]," ",) ,IF(AND(RC[-29]0,RC[-26]0,RC[-76]="HAMBK",LEFT(RC[-16],16)"ZWISCHENRECHNUNG") ,CONCATENATE("Storage Days"," ","="," ",RC[-97]," ","/"," ","Free Time"," ","="," ",RC[-33]," ","Day(s) "," ","/"," "," ",RC[-32]," ","Days"," ","@",RC[-29]/RC[-30]," ","per Day"," ","&"," ",RC[-27]+RC[-24]+RC[-21]," ","Day(s) "," ","@"," ",(RC[-26]+RC[-23]+RC[-20])/(RC[-27]+RC[-24]+RC[-21]) ," ","per Day"," ","/"," ",R1C[-92]," ",RC[-92]," ","/"," ",R1C[-90]," ",RC[-90]," ",) ,IF(AND(RC[-29]0,RC[-26]=0,RC[-76]="HAMBK",LEFT(RC[-16],16)="ZWISCHENRECHNUNG") ,CONCATENATE("preliminary invoice after"," ",RC[-97]," ","day(s) / final invoice will follow once container picked up: Storage Days"," ","="," ",RC[-97],"
","/"," ","Free Time"," ","="," ",RC[-33]," ","Day(s)"," ","/"," ",RC[-32]," ","Day(s)"," ","@",RC[-29]/RC[-30]," ","per Day"," ","/"," ",R1C[-92]," ",RC[-92]," ","/"," ","No Out Date"," ","Calculation till"),IF(AND(RC[-29]0,RC[-26]0,RC[-76]="HAMBK",LEFT(RC[-16],16)="ZWISCHENRECHNUNG"),CONCATENATE("preliminary invoice after"," ",RC[-97]," ","day(s) / final invoice will follow once container picked up: Storage Days"," ","="," ",RC[-97]," ","/"," ","Free Time"," ","="," ",RC[-33]," ","Day(s)"," ","/"," "," ",RC[-32]," ","Days"," ","@",RC[-29]/RC[-30]," ","per Day"," ","&"," ",RC[-27]+RC[-24]+RC[-21]," ","Day(s)"," ","@"," ",(RC[-26]+RC[-23]+RC[-20])/(RC[-27]+RC[-24]+RC[-21])," ","per Day"," ","/"," ",R1C[-92]," ",RC[-92]," ","/"," ","No Out Date"),))))

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hi
was hast du genau vor?
ich vermute dass du die Formel per Makro in eine Zelle schreiben willst.
das müsste bei längeren Formeln so funktionieren:

Cells(1, 1).FormulaR1C1 = "=IF(AND(RC[-29]0,RC[-26]=0," & _
"RC[-76]=""HAMBK"",LEFT(RC[-16],16)""ZWISCHENRECHNUNG"") " & _
",CONCATENATE("Storage Days"","" "",""="","" "",RC[-97],""...

also die Teilstrings für jede Zeile immer mit Anführungszeichen begrenzen (am Anfang und am Ende), das & als Verbindung nicht vergssen und bei Zeilenende dann Leerzeichen + Unterstrich.
Die Anführungzeichen, die zur Formel gehören nüssen immer verdoppelt werden.
Einfache Anführungszeichen begrenzen den Textstring.
Ich finde solche Monsterzeilen mit "_" aber eher ungünstig und bevorzuge die Variablen-Vairante.
Denn auch VBA-Zeilen die mit "_" umgebrochen werden, können nicht unbegrenzt lang sein, mit der Variable kann man die VBA-Zeilen auch bei längeren Formeln kurz halten:

dim FO as string
FO = "=erster Teil der formel"
FO = FO & "zweiter Teil der Formel"
FO = FO & "dritter Teil der Formel"
Cells(1, 1).Formula = FO

Allerdings ist deine Formel mit 1800 Zeichen sehr lang.
Excel kann zwar auch deutlich längere Formeln verarbeiten, aber beim Übertragen der Formel mit VBA nach Excel gibt es eine starke Begrenzug bezüglich der Länge und solche Formeln müssen deutlich kürzer sein.
Aber auch hier gibt es einen Trick:
Schreibe die Formel nicht als Formel in die Zelle, sondern als Text.
lasse hierzu den Formeltext nicht mit "=" beginnen, sondern mit einer Zeichenfolge, die in der Formel so nicht vorkommt: "xxxIF(AND...")´
Beim Schreiben von Texten per VBA gibt es keine VBA-spezifischen Längenbeschränkungen wie beim Schreiben von Formeln.
Ersezte dann in der Zelle mit der Menüfunktion ERSETZEN bzw mit dem VBA-Befehl: REPLACE das "xxx" durch das "=".
Damit läuft das Erstellen der Formel in Excel und es gilt die Excelspezifische Längenbeschränkung, nicht die VBA-spezifische.
Wenn du das Replace per VBA ausführst, muss die Formel in Englisch mit A1-Zellbezügen geschrieben sein.
Wenn du das Ersetzen manuell machst, in deutsch (Landessprache)
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)

VBA Formel Zeilenumbrüche

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