Fromel per VBA zuweisen

Hallo, ich habe ein Formel in Excel die auch sehr gut funktioniert und genau das macht was sie machen soll. Jetzt habe ich 2 Probleme:
1. Wenn ich die Formel einem Zellbereich per VBA zu weisen möchte verändert VBA die Formel von
Richtig: =WENN(ISTZAHL(SUCHEN("/";J3));LINKS(J3;SUCHEN("/";J3)+1);LINKS(J3;SUCHEN("-";J3)-1)) in
Falsch =WENN(ISTZAHL(SUCHEN(" / ";J3));LINKS(J3;SUCHEN(" / ";J3)+1);LINKS(J3;SUCHEN(" - ";J3)-1))
Dadurch kommt es natürlich auch zu falschen Ergebnissen
Wie kann ich verhindern das VBA automatisch Leerzeichen vor dem / und dem - in der Formel setzt?
2. Ich habe eine leerspalte J in der überall die Formel (siehe oben) gesetzt werden soll, wenn in der benachbarten Zelle der Spalte I ein Wert steht. Ich habe das versucht so zu lösen
Dim letzte As Long
letzte = Range("J65536").End(xlUp).Row
Range("I3:I" & letzte).FormulaLocal = "=WENN(ISTZAHL(SUCHEN(" / ";J3));LINKS(J3;SUCHEN(" / ";J3)+1);LINKS(J3;SUCHEN(" - ";J3)-1))"
End Sub
Wie gesagt, hier wird in die Formel von VBA Leerzeichen eingepflegt und zum anderen kommt es zu der Meldung "Laufzeitfehler 13 Typen unverträglich" . Ich weis nun nicht ob dieser Fehler auf den Leerzeichen beruht. Mit einer anderen Formel mit Sverweis kommt dieser Fehler nämlich nicht.
Gib es einen besseren Weg Formeln in einen dynamischen Bereich einzufügen?
Vielen Dank jetzt schon einmal
Gruß Peter

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hi
Ist schon der richtige Weg, um eine Formel einzufügen. Du musst nur beachten, dass du in VBA die Anführungszeichen, die Teil der Formel sind, immer verdoppeln musst.
Das ist in VBA so die Konvention: einfache Anführungszeichen kennzeichnen Start und Ende eines Textes, doppelte Anführungszeichen sind Teil des Textes
Das muss dann so aussehen:


.FormulaLocal = "=WENN(ISTZAHL(SUCHEN(""/"";J3));..."

Mit den einfachen Anführungszeichen und dem / dazwischen sieht es für VBA so aus, als wolltest du den Text "=WENN(ISTZAHL(SUCHEN(" durch den Text ";J3));LINKS(J3;SUCHEN(" dividieren.
Unabhängig davon würde ich die Formel nicht in .FormulaLocal angeben, sondern in .FormulaR1C1.
Das hätte folgende Vorteile:
1. der Recorder zeichnet das so auf, inklusive der gedoppelten Anführungszeichen.
2. das Makro läuft in jeder Länderversion, nicht nur in einem deutschen Excel.
3. mit R1C1-Adressen ist die Formel in der Regel unabhängig von der Zelle, in der sie eingefügt wird.
Dh wenn du beschließt, dass die Formel nicht mehr in Zeile 3 eingefügt wird sondern erst in Zeile 4, musst du nicht nur im Range("I3:...) aus der 3 eine 4 machen, sondern auch im Formeltext.
Arbeitest du mit .FormulaR1C1 oder auch .FormulaR1C1Local (deutsch), so wäre die Adresse für den Bezug auf die rechte Nachbarzellen das RC[1] bzw ZS(1), welches immer gleich bleibt, egal in welche Zelle man die Formel schreibt.
R1C1 ist für uns zwar ungewohnt, hat aber in der Programmierung den Vorteil, dass es relative Zellbezüge auch relativ beschreiben kann und damit die Formel unabhängig von der Zielzelle ist.
In A1 müssen auch relative Bezüge mit absoluten Adressen beschrieben werden, was zur Folge hat, dass die relativen Bezüge immer an die Zielzelle angepasst werden.
Ist die Zielzelle bekannt und fest, ist es egal, aber manchmal kommt es auchbvor, dass die Zielzelle unbekannt ist und erst im Makro ermittelt werden muss. Dann hat R1C1 eben den Vorteil, dass der Formeltext fix bleibt, während du ihn bei A1-Adressen anpassen musst.
Kleines Beispiel:
Du willst am Ende einer Spalte die Summe über diese Spalte einfügen, die Anzahl der Werte in der Spalte ist variabel.
Mit A1-Adressen sieht das so aus:


Zeile = Cells(Rows.Count, 1).End(xlup).row + 1
Cells(Zeile, 1).Formula = "=Sum(A1:A" & Zeile -1 & ")"

Mit R1C1 reicht das:


Cells(Rows.Count, 1).end(xlup).Offset(1, 0).FormulaR1C1 = "=Sum(R1C:R[-1]C)"

Grus Daniel

Beliebteste Forumthreads (12 Monate)

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread