Microsoft Excel

Herbers Excel/VBA-Archiv

Variable in Formel einfügen

Betrifft: Variable in Formel einfügen von: Tanja
Geschrieben am: 08.09.2014 11:22:41

Hallo Forum,

ich muss mal wieder um eure Hilfe bitten! Habe zu diesem Thema bereits einige Anfragen im Netz gefunden und verschiedenste Möglichkeiten die dort funktionierten ausprobiert - leider bei mir ohne Erfolg.

Ich möchte durch ein Makro eine Formel in eine Zelle schreiben. Durch die Variable end_row2 ermittle ich die letzte Zeile meiner Tabelle (Ausgabe getestet, kommt z.B. der Wert 160). Folgende Formel soll eingefügt werden:

=Summe(JobCount!G2:JobCount!G 160 bzw. end_row2) /Summe(JobCount!H2:H 160 bzw. end_row2)

Im Makro getestet hatte ich bisher verschiedenste Varianten, z.B.

Range("E7").FormulaLocal = "=Summe(JobCount!G2:JobCount!G""'"end_row2"'"")/Summe(JobCount!H2:JobCount!H'"end_row2&"')"

Range("E7").FormulaLocal = "=Summe(JobCount!G2:JobCount!G"&end_row2&")/Summe(JobCount!H2:JobCount!H"&end_row2&")"

Es muss doch hierfür eine eindeutige bzw. immer funktionierende Regel geben?!?

Hier der Link zur Bsp.-Datei:

https://www.herber.de/bbs/user/92521.xlsm

  

Betrifft: AW: Variable in Formel einfügen von: Daniel
Geschrieben am: 08.09.2014 11:34:51

Hi

Formlen sind Stringtexte.
Wenn du String-Texte aus festen Bestandteilen und Variablen zusammensetzen willst, dann geht das so:

="Fester Bestandteil 1" & Variable1 & "Fester Bestandteil 2" & Variable2

dh den Festen Bestandeil immer mit einem Anführungszeichen Beenden und dann mit & verketten.
Beachte hierbei die Leerzeichen ovor und nach dem &, die sind notwendig und du musst sie eingeben.

desweiteren hat deine Formel noch den Fehler, dass du in der Zelladresse den Blattnamen vor der Zelle unten-rechts wiederholst.
Hast du sowas schon mal in einer normalen Zelle gesehen?

sieht in deinem Fall so aus:
end_row2 = 160
Range("E7").FormulaLocal = "=Summe(JobCount!G2:G" & end_row2 & ")/Summe(JobCount!H2:H" & end_row2 &")"

die zweite Methode ist, die Formel selbst erstmal in eine Variable zu schreiben und dort wo die Variable hin soll, einen Dummy-Text zu verwenden ("xxx").
Dann ersetzt man den Dummy-Text durch den Variablenwert.
Formel = "=Summe(JobCount!G2:Gxxx)/Summe(JobCount!H2:Hxxx)"
end_row2 = 160
Formel = Replace(Formel, "xxx", end_row2)
Range("E7").FormulaLocal = Formel
ich finde die zweite Variante übersichtlicher, vorallem wenn eine Variable mehrfach in der Formel verwendet wird.

Gruß Daniel


  

Betrifft: AW: Variable in Formel einfügen von: Tanja
Geschrieben am: 08.09.2014 11:42:06

So hat es nun endlich funktioniert:

Range("E7").FormulaLocal = "=Summe(JobCount!G2:G" & end_row2 & ")/Summe(JobCount!H2:H" & end_row2 & ")"

Vielen Dank Daniel für die gute Erklärung! Ich hatte schon so viel verschiedenes probiert, dass ich gar nicht mehr wusste wie. Ohne den zweiten Blattnamen hatte ich es vorher die ganze Zeit probiert... dachte wenn es so nicht geht, probiere ich mal ihn zu wiederholen. Wie gesagt, habe alles erdenkliche probiert, nur nicht auf Leerzeichen geachtet.

Vielen vielen Dank für die schnelle und gute Hilfe!!!


 

Beiträge aus den Excel-Beispielen zum Thema "Variable in Formel einfügen"