Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1852to1856
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Problem bei Formel in Zelle per VBA

Problem bei Formel in Zelle per VBA
08.11.2021 18:06:00
Andreas
Hallo zusammen, ich möchte per VBA eine Formel in die Spalte eines Tabellenblatts einfügen.
Leider scheitert der Versuch mit:

Sub Test()
lz = Cells(Rows.Count, 1).End(xlUp).Rows.Row
Range("AE2:AE" & lz).Formula2Local  "=WENN(W2"";1;"0")+WENN(X2"";1;"0")+WENN(Y2"";1;"0")+WENN(Z2"";1;"0")+WENN(AA2"";1;"0")+WENN(AB2"";1;"0") "
End Sub
da auch noch andere Formeln an anderen Stellen folgen sollen, wäre ich für eine Lösung dankbar in der ich die deutsche Schreibweise der
Formeln verwenden könnte. (Vielleicht gibts ja auch einen Formel-Translater?)
An anderer Stelle hatte ich es bereits gelesen dass es wohl mit den Anführungszeichen zu tun haben soll? Komme aber leider nicht damit
weiter. Die Formel habe ich jetzt 1zu1 aus der Zelle kopiert. -> Wahrscheinlich müsste die dann noch entsprechend beim Range mit lz angepasst
werden, aber ich denke der Fehler liegt nicht daran. Würde mich über eine Hilfestellung sehr freuen... danke vorab!

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Problem bei Formel in Zelle per VBA
08.11.2021 18:12:54
{Boris}
Hi,
die korrekte Schreibweise mit den "" wäre:

.Formula2Local = "=WENN(W2"""";1;""0"")+WENN(X2"""";1;""0"")+WENN(Y2"""";1;""0"")+WENN(Z2"""";1;""0"")+WENN(AA2"""";1;""0"")+WENN(AB2"""";1;""0"") "
Wobei Du die Nuller ("0") nicht in Anführungszeichen schreiben solltest, da ansonsten prinzipiell Text (statt Zahl) rauskommt, was Du in Deiner Formel nur dadurch "aufhebst", dass Du die Einzelteile mit dem Operator + verbindest.
VG, Boris
AW: Problem bei Formel in Zelle per VBA
08.11.2021 18:28:19
Andreas
Leider kommt auch bei der Variante "Laufzeitfehler 1004" - Anwendungs oder Objektdefinierter Fehler...
Habs auch mal versucht die ""0""" in 0 zu wandeln, da gehts auch nicht.
Anzeige
AW: Problem bei Formel in Zelle per VBA
08.11.2021 18:16:15
Hajo_Zi
ich bin malk davon ausgegagen Du möchtest die Zahl 0 einztragen und nicht den Text "0"

Option Explicit
Sub Test()
Dim Lz As Long
Lz = Cells(Rows.Count, 1).End(xlUp).Rows.Row
Range("AE2:AE" & Lz).FormulaLocal = "=WENN(W2"""";1;0)+WENN(X2"""";1;0)" _
& "+WENN(Y2"""";1;0)+WENN(Z2"""";1;0)+WENN(AA2"""";1;0)+WENN(AB2"""";1;0)"
End Sub
GrußformelHomepage
Anzeige
AW: Problem bei Formel in Zelle per VBA
08.11.2021 18:30:10
Andreas
Hallo zusammen jetzt klappts euch allen vielen lieben Dank !!!
Zur Formel selbst...
08.11.2021 18:22:27
{Boris}
Hi Andreas,
Deine Formel sieht danach aus, dass sie auch mit

.Formula2Local = "=ANZAHL2(W2:AB2)"
angegeben werden kann - vorausgesetzt in W2:AB2 stehen keine Formeln, die einen Leerstring zurückgeben.
VG, Boris
AW: Problem bei Formel in Zelle per VBA
08.11.2021 18:24:18
Daniel
Hi
Die Dopplung der Anführungszeichen hat Boris ja schon genannt.
Einen Formel-Translator hat Excel eingebaut:
Schreibe die Formel normal in die Zelle.
Du kannst dir dann die Formel im Direktfenster in jeder der 4 möglichen Schreibweisen anzeigen lassen.

?Selection.Formula
?Selection.FormulaR1C1
?Selection.FormulaLocal
?Selection.FormulaR1C1Local
Ich empfehle .FormulaR1C1.
Das funktioniert dann unabhängig von der Länderversion und dadurch, dass man relative Zellbezüge auch direkt relativ angeben kann, wird die Erstellung der Formel oft einfacher, vor allem dwenn die Zielzelle ggf erst im Code ermittelt werden muss.
Auch das Problem mit den Anführungszeichen kann man so einfach lösen und sich diese auch gleich mit ausgeben lassen, so dass man das Direktfensterergebnis 1:1 in den Code kopieren kann:

?Replace(Selection.FormulaR1C1, """", """""")
Gruß Daniel
Anzeige
AW: Problem bei Formel in Zelle per VBA
08.11.2021 18:55:40
Andreas
hmmmm zu früh gefreut? Zunächst nochmals vielen Dank für die Hilfe mit der Formel,
nur wirds jetzt schwierig die Variable da rein zu bekommen, eben dass Zeile für Zeile abgefragt wird.
So macht der mir das zwar zeile für zeile aber in der Formel wird mir das "zu genau" genauso eingetragen.
In Zeile 2 sollte ja jetzt W2, in Zeile 3 dann W3 etc. stehen. Nun steht da immer W & i ;-(
vermutlich weil ich ja sage trage das genauso ein. Nur würde ich es ja im Tabellenblatt
nur in der ersten Zeile eintragen und dann unten die Ecke der Zelle anklicken...
lz = Cells(Rows.Count, 1).End(xlUp).Rows.Row
Dim i As Integer
For i = 2 To lz
Range("AE" & i).FormulaLocal = "=WENN(W &i"""";1;0)+WENN(X &i"""";1;0)" _
& "+WENN(Y &i"""";1;0)+WENN(Z &i"""";1;0)+WENN(AA &i"""";1;0)+WENN(AB &i"""";1;0)"
Next i
Anzeige
AW: Problem bei Formel in Zelle per VBA
08.11.2021 19:14:55
Daniel
Hi
Naja ich schrieb doch, dass ich R1C1 bevorzuge und auch warum.
Lass dir mal wie von mir beschrieben ein paar untereinander liegende Formeln dieser Art in R1C1 (gerne auch R1C1Local anzeigen, und du wirst schnell sehen, dass du hier diese Zeilenvariable gar nicht benötigst.
Auch musst du die Formel nicht in jede Zeile einzeln per Schleife schreiben, denn die Formel ist für alle Zellen die selbe (was man mit R1C1 auch sofort sieht), sondern du kannst daher die Formel auch in alle Zellen gleichzeitig schreiben:
Das funktioniert sogar mit .FormulaLocal, hier muss man dann die Zellbezüge für die erste Zelle passend angeben:

lz = Cells(Rows.Count, 1).End(xlUp).Rows.Row
Range("AE2:AE" & lz).FormulaLocal = "=WENN(W2"""";1;0)+WENN(X2"""";1;0)" _
& "+WENN(Y2"""";1;0)+WENN(Z2"""";1;0)+WENN(AA2"""";1;0)+WENN(AB2"""";1;0)"
Solltest du wirklich mal eine Variable in der Formel benötigen, dann musst du das hier genauso machen, wie du es bei der Adresse für die Range in der Schleife ja schon richtig gemacht hast (dem. Range("AE" & i)
Den festen Text mit Anführungszeichen beenden und dann die Variable mit & verketten.
Das ist beim Text für die Formel nicht anders als beim Text für die Zelladresse in der Range, denn beides ist im Prinzip das gleiche, nämlich Text und muss daher auch nach den gleichen Regeln behandelt werden, die da lautet:
Variablen dürfen niemals innerhalb von Anführungszeichen stehen, sondern müssen mit & mit dem weiteren Text verkettet werden
Gruß Daniel
Anzeige
AW: Problem bei Formel in Zelle per VBA
08.11.2021 19:26:55
Andreas
Hallo Daniel, das Prinzip von R1C1 ist mir grob bekannt, daher verstehe ich auch an genau dem Problem den Vorteil der Variante.
Du hast mir in den ersten Beitrag geschrieben, ich könne mir die Formel mit
?Selection.FormulaR1C1
entsprechend übersetzen lassen.
Leider kenne ich die Funktion des "Direktfensters" nicht. Ist es dir möglich mir da ne kleine Hilfestellung zu geben?
Muss der Code ?Selection.FormulaR1C1 in das Direktfenster rein? Mit oder ohne der Formel? Irgendeine Taste drücken?
Ich kenne das leider gar nicht :-)
Ich versteh Dein Problem nicht...
08.11.2021 19:53:19
{Boris}
Hi,
...abgesehen davon, das Du bei Daniel in den allerbesten Händen bist und ich Dir nur raten kann, seine Vorschläge (nicht nur hier) zu beherzigen:

Range("AE2:AE" & lz).Formula2Local = "=WENN(W2"""";1;""0"")+WENN(X2"""";1;""0"")+WENN(Y2"""";1;""0"")+WENN(Z2"""";1;""0"")+WENN(AA2"""";1;""0"")+WENN(AB2"""";1;""0"") "
funktioniert einwandfrei - und zwar auch relativ. Du kannst natürlich auch die Variante ohne "2" verwenden - also nur FormulaLocal.
Was genau geht bei Dir nicht?
VG, Boris
Anzeige
AW: Ich versteh Dein Problem nicht...
08.11.2021 21:28:12
Andreas
Hallo Boris, danke dass du fragst... also die Formel an sich funktioniert. nur habe ich jetzt ja das Problem dass die für die einzelnen Zeilen entsprechende Variablen braucht, Zeile 2 = W2 , Zeile 3 = W3 etc. Manuell erstelle ich die Formel ja in der Zelle und ver-autovollständige ich diese dann per doppelklick dann nach unten.
Dass ich bei euch (Daniel und Dir) "in guten Händen" bin merke ich nicht nur an diesem Post. Etwas zu können ist das eine, es einem andern dann noch zu erklären das andere.. von daher danke für die Hilfestellungen !!!
AW: Ich versteh Dein Problem nicht...
08.11.2021 21:40:30
{Boris}
Hi Andreas,
hast Du es denn mal getestet?
Wenn die Variable lz z.B. den Wert 5 hat, dann wird mit

Range("AE2:AE" & lz).Formula2Local = "=WENN(W2"""";1;""0"")+WENN(X2"""";1;""0"")+WENN(Y2"""";1;""0"")+WENN(Z2"""";1;""0"")+WENN(AA2"""";1;""0"")+WENN(AB2"""";1;""0"") "
in die Zellen AE2, AE3, AE4 und AE5 die Formel eingetragen - und zwar in AE2 mit Bezug auf W2, in AE3 auf W3, AE4 auf W4 usw. - sie verhält sich also komplett relativ - genau so wie Du es haben möchtest.
VG, Boris
Anzeige
AW: Problem bei Formel in Zelle per VBA
08.11.2021 21:28:52
Andreas
Danke ... schau ich mir auch noch an, bin jetzt bei paar YT Videos gelandet ...
AW: Problem bei Formel in Zelle per VBA
08.11.2021 19:59:40
Daniel
Naja die Funktionen des VBA-Editors hier im Forum textlich zu erklären ist mühsam
Das Direktfenster ist ein Fenster im VBA-_Editor ähnlich dem Codefenster.
Hier kannst du einzelne VBA-Befehle direkt eingeben und sofort ausführen lassen, im Prinzip so wie früher die DOS-Kommandozeile.
Das Direktfenster kannst du über "Ansicht" einblenden, falls du es nicht hast.
Die Taste zum abschließen einer Eingabe ist ENTER.
Gruß Daniel
Anzeige
AW: Problem bei Formel in Zelle per VBA
08.11.2021 21:30:04
Andreas
Hallo und nochmal besten Dank für den Hinweis mit dem Fenster, ich habe es so einigermaßen hinbekommen. In YT habe ich Videos gefunden, eins
war auch schon relativ nahe an meiner Situation.

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige