Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1468to1472
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

per VBA Formel mit Sonderzeichen in Zelle schreibe

per VBA Formel mit Sonderzeichen in Zelle schreibe
13.01.2016 16:45:40
Felix
Hallo,
ich möchte gern per VBA eine Formel in eine Zelle schreiben. Die Formel soll heißen:
=MITTELWERTWENN(E2:E527;"<" & Übersicht_Parameter!$B$23;C2:C527)
wobei ich die "527" jeweils dynamisch bestimme (ausgefüllte Zeilen). Mein VBA Code sieht dann so aus:
WS.Cells(1, 3) = "=MITTELWERTWENN(E2:E" & iLastRow & ";" & Chr(34) & Chr(60) & Chr(34) & Chr(32) & Chr(38) & Chr(32) & "Übersicht_Parameter!$B$23;" & "C2:C" & iLastRow & ")"
Wenn ich das tue (habe auch versucht, es vorher in einen string zu schreiben und diesen dann in die Zelle zu schreiben), bekomme ich diese Fehlermeldung:
Laufzeitfehler '1004': Anwendungs- oder objektdefinierter Fehler
Ich habe nach einigem probieren versucht, ein Hochkomma (') vor die Formel zu schreiben. Das geht. Wenn ich dann in Excel manuell in die Zeile gehe, das Hochkomma lösche und Enter drücke, funktioniert das auch einwandfrei, berechnet das richtige etc.. Wenn ich allerdings versuche, per VBA das Hochkomma zu löschen, bekomme ich (naja, mittel-überraschend) den gleichen Fehler wie oben.
Wieso funktioniert die Formel (tut sie ja, wenn ich das Hochkomma manuell lösche), aber ich kann sie nicht per VBA eintragen? Bzw. was muss ich ändern, damit es geht?
Vielen Dank schonmal! :)

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: per VBA Formel mit Sonderzeichen in Zelle schreibe
13.01.2016 17:05:48
Daniel
Hi
Excel und VBA sprechen englisch mit einander.
wenn du die Formel auf diese Weise in die Zellen schreibst, muss die Formel mit englsichen Funktionsnamen, dem Komma als Parameter- und dem Punkt als Dezimalzeichen (bei Zahlen) geschreiben werden.
wenn du die Formel in der gewohnten deutschen Schreibweise im Code schreiben willst, dann musst du VBA mitteilen über .FormulaLocal
der Aufwand den du da betreibst, ist überflüssig, denn alle Zeichen die du eingeben willst, kannst du über die Tastatur erreichen.
Probleme macht nur das Anführungszeichen (Chr(34)) weil es zwei Funktionen hat, nämlich normales Zeichen im Text und Kennzeichnung für das Ende des Textstrings.
in VBA ist dieses Problem so gelöst, dass man Anführungszeichen die Bestandteil des Textes sind, verdoppelt.
An dieser Verdopplung erkennt dann der Editor, dass hier der Text nicht zu Ende ist.
sieht dann so aus:
WS.Cells(1, 3).FormulaLocal "=MITTELWERTWENN(E2:E527;""
Gruss Daniel

Anzeige
AW: per VBA Formel mit Sonderzeichen in Zelle schreibe
13.01.2016 17:38:33
Luschi
Hallo Felix,
bei mir klappt das so:

ws.Cells(1, 3).FormulaLocal = "=MITTELWERTWENN(E2:E" & iLastRow & ";" & _
Chr(34) & Chr(60) & Chr(34) & Chr(32) & Chr(38) & Chr(32) & _
"'Übersicht_Parameter'!$B$23;" & "C2:C" & iLastRow & ")"
'oder
ws.Cells(1, 3).FormulaLocal = "=MITTELWERTWENN(E2:E" & iLastRow & ";" & _
Chr(34) & "
Gruß von Luschi
aus klein-Paris
PD: Daniel's Lösungsversuch funktioniert bei mir nicht.

AW: per VBA Formel mit Sonderzeichen in Zelle schreibe
13.01.2016 18:02:09
Daniel
dann hast du das Gleichheitszeichen vergessen.
das schöne an solchen Fehlern ist, dass man sehr schnell erkennt, wer versucht das Prinzip zu verstehen und dann den Code dann selber schreibt, und wer nur Copy-Paste benutzt ohne mitzudenken.
Gruß Daniel

Anzeige
Allgemein üblich wäre es ja auch, ...
13.01.2016 19:52:25
Luc:-?
Luschi,
für die US-Original-Fml die Range-Objekt-Eigenschaft .Formula zu benutzen, genau wie .FormulaLocal für die lokale Fml-Form. Allerdings hat Daniel recht und es fktioniert tatsächlich auch so (natürlich mit führendem = oder @, Letzteres nur, weil eine Fkt unmit­telbar folgt). Das ist im Ggsatz zu manuellem FmlEintrag vermutlich einerseits aus dem von Daniel angeführten Kommunika­tionsgrund und andererseits so, weil Xl in diesem Fall eine automatische Konvertierung in die FmlForm lt lokaler Einstellung „nach­schiebt“, was die Fml dann auch gleich aktiviert. Das ist bei DirektEintrag der lokalen FmlForm natürlich nicht so. Dass es sich um eine gültige Fml handelt, erkennt Xl erst bei Edit-Klick, eigentlich nur, wenn kein TextPräfix davorsteht.
Übrigens, Daniel,
war man hierbei ja gezwungen, die Fml selbst zu schreiben, weil man ja übersetzen musste, auch, wenn man sie ursprünglich kopiert hatte. Aber ob Luschi dabei das = vergessen hat oder doch eher etwas Anderes falsch gemacht hat, kann nur er wissen.
Und Felix,
die Leerzeichen um & herum sind nun wirklich überflüssig. Die gültige Form nach Daniels 1.Vorschlag sähe dann vereinfacht so aus:
WS.Cells(1, 3) = "=averageIf(E2:E" & iLastRow & ",""<""&Tabelle2!$B$23,C2:C" & iLastRow & ")"
Gruß, Luc :-?
Besser informiert mit …

Anzeige
AW: Allgemein üblich wäre es ja auch, ...
14.01.2016 07:07:38
Luschi
Hallo Daniel & Luc,
natürlich funktioniert die Lösung von Daniel, irgendwie ist das von Euch schon Vermutete eingetreten und das '=' bei ...FormulaLocal war verschwunden.
Allerdings kann ich mich der Meinung von Luc:
Zitat:

die Leerzeichen um & herum sind nun wirklich überflüssig.
nicht anschließen. Ein Formel in der Bearbeitungsleiste sollte auch gut lesbar und nicht nicht so verdichtet sein, daß man erst mehrere Male genau hinschauen muß, ehe man kapiert, was Sache ist.
Größten Ärger hätte ich mit dieser CodeZeile bekommen:

WS.Cells(1, 3) = "=averageIf(E2:E" ...

Der CoKo (Code-Kontrolleur) hätte Folgendes beanstandet:
- fehlende Eigenschaft '.Formula'
- nicht korrekte Schreibweise von 'averageIf' statt 'AVERAGEIF'
Dafür gab es Programierrichtlinien in der Firma:
- CamelCase für Eigenschaft/Property
- pascalCase für Sub/Function
- zusätzliche Leerzeichen in Excel-Formeln zur Verbesserung der Lesbarkeit usw.usf.
Deshalb sieht Luc's Code bei mir so aus:

WS.Cells(1, 3).Formula = "=AVERAGEIF(E2:E" & iLastRow & _
", ""
Gruß von Luschi
aus klein-Paris

Anzeige
Tja, der VBE beanstandet das nicht (Xl14) ;-) owT
14.01.2016 11:37:53
Luc:-?
:-?

AW: per VBA Formel mit Sonderzeichen in Zelle schreibe
14.01.2016 09:07:09
Felix
Vielen Dank an alle! Hat funktioniert :)
Das mit den ganzen Chr() hatte ich am Anfang auch nicht in dem Ausmaß - war dann der Versuch, das Problem zu beheben...
Wieder was gelernt, Vielen Dank! :)

381 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige