Microsoft Excel

Herbers Excel/VBA-Archiv

Dynamische Formel aus VBA in Excel

Betrifft: Dynamische Formel aus VBA in Excel von: Markus
Geschrieben am: 02.11.2020 10:23:12

Hallo liebe Foristen,

zuerst will ich mich mal vorstellen, da das mein erster Eintrag in diesem Forum ist. Mein Name ist Markus, und ich habe die letzten Monate berufsbedingt meine Excel-Kenntnisse massiv ausbauen dürfen 

Jetzt bin ich auf ein Problem gestossen, das sich meiner Recherche nach nicht mit Excel alleine lösen lässt und ich auf VBA angewiesen bin.


In folgendem Tabellenabschnitt soll passieren:



Letzte Zeile in Spalte E auslesen und den Inhalt auf die Seite schreiben  OK gelöst mittels INDEX und VERGLEICH.

Dieser Wert soll jetzt in die selbe Zeile aber in Spalte F geschrieben werden. Da sich die Zeile aber immer ändert habe ich folgenden Code geschrieben, der leider nicht funktioniert…





Ausserdem soll in allen Zeilen über der Letzten jeweils eine Formel eingetragen werden, die sich ebenfalls dynamisch mitändert.

In Zeile 16 soll stehen = Wert aus E17

In Zeile 15 soll stehen =WENNFEHLER(F16-D16;"#NV")

In Zeile 14 soll stehen =WENNFEHLER(F15-D15;"#NV")

Usw bis rauf zur ersten Zeile der Tabelle.


Habt ihr einen Vorschlag wie ich das lösen könnte?

Betrifft: AW: Dynamische Formel aus VBA in Excel
von: Daniel
Geschrieben am: 02.11.2020 10:34:40

Hi

Trage die Formeln mal von Hand ein und zeichne mit dem Recorder auf. Setze dabei alle Zellbezüge, die nicht zwingend relativ sein müssen auf absolut.
Verwende dann diese Schreibweise zum eintragen von Formeln in deinem Code.

Bei .FormulaLocal musst du auch in VBA die Formel genau so angeben, wie du sie in deinem Excel auch eingeben würdest, entsprechend der Ländereinstellung.
Eine Ländereinstellung, die deutsches Semikolon als Parametertrennzeichen mit englischen Funktionsnamen kombiniert, wäre mir neu.

Gruß Daniel

Betrifft: AW: Dynamische Formel aus VBA in Excel
von: Markus
Geschrieben am: 02.11.2020 13:04:35

Hallo Daniel,
danke für deine schnelle Antwort, damit hätte ich gar nicht gerechnet.

aktuell habe ich das Problem folgendermassen adressiert:


  • 'letztezeile = Worksheets("WOWR DATA").Cells(Rows.Count, 5).End(xlUp).Row

  • 'vorletzteZeile = letztzeile - 1

  • letzteZeile = Worksheets("WOWR Data").Cells(4, 6).Value

  • letzteZeileWert = Worksheets("WOWR Data").Cells(4, 5).Value

  • Range("F" & letzteZeile).Value = letzteZeileWert


  • dadurch, dass ich mich in einer intelligenten Tabelle befinde, darf ich die Formel gar nicht eintragen, da mir ja ansonsten die Formel für die gesamte Tabelle übernommen wird.
    Für das erstmalige ausführen der Tabelle funktioniert es jetzt, da die Zeilen oberhalb meiner Zieltabelle bereits die korrekte Formel enthalten.
    wenn ich jetzt allerdings eine neue Zeile erstelle, steht in der "alten letzten zeile" keine Formel mehr, sondern der vorher eingesetzte Wert.

    Daher muss in jedem Zyklus die Formel in alle Zeilen oberhalb der Zielzelle neu eingesetzt werden.

    ich würde das irgendwie als Schleife verstehen:
    für alle Zelle in Spalte F von 6 bis (letztezeile-1) muss Spalte F minus Spalte D der Vorzeile eingetragen werden.

    irgendwie hänge ich da noch...

    die erste Zeile kann ich statisch machen, da sich diese nicht verschiebt.
    die vorletzte Zeile hätte ich ermittelt indem ich

    'letztezeile = Worksheets("WOWR DATA").Cells(Rows.Count, 5).End(xlUp).Row
    'vorletzteZeile = letztzeile - 1


    laufen lassen würde. und dann jeweils so etwas wie
  • Range("F" 6 bis vorletztzeile.Formula ="..."

  • wobei die Formel natürlich in jeder Zeile anders ausschaut..

    ich glaub ich hab mir da ein für einen Anfänger recht komplexen Fall ausgesucht :)

    Betrifft: AW: Dynamische Formel aus VBA in Excel
    von: Daniel
    Geschrieben am: 02.11.2020 13:10:18

    Mit diesen intelligenten Tabellen bin ich auch noch nicht sorichtig vertraut.
    Deshalb fällt es mir da schwer was zu zu sagen, zumal ich dein Projekt und die Aufgabenstellung nicht kenne.
    Gruß Daniel

    Betrifft: AW: Dynamische Formel aus VBA in Excel
    von: Markus
    Geschrieben am: 02.11.2020 13:28:25

    Hallo Daniel,
    die intelligente Tabelle hat den Vorteil, dass er eine Formel, die an einer Stelle der Spalte eingetragen wird, auf die ganze Spalte anwendet. und genau das wird mir jetzt zum Verhängnis.

    Kurz zusammengefasst habe ich:

  • das Datum (Spalte A)

  • eine Anzahl an Arbeitsaufträgen die geöffnet werden (Spalte B)

  • eine Anzahl an Arbeitsaufträgen die geschlossen werden (Spalte C)

  • die Differenz der Beiden (Spalte D)

  • Den Stand der aktuell offenen Arbeitsaufträge (Spalte E)


  • die Daten A,B stammen aus einer SQL Datenbank die ich mittels Query in Excel habe.
    Die Differenz ist berechnet und die Anzahl der offenen Aufträge komtm auch aus der Query.

    Was ich jetzt möchte ist aus dem Stand der aktuell offenen Arbeitsaufträge und der Differenz jeweils die Anzahl der offenen Aufträge in der Vergangenheit (jeweils pro Monat darstellen.

    Die Datenbank gibt mir ja immer nur den ist-Zustand und überschreibt ihre alten Daten immer wieder.

    Hoffe es ist etwas klarer



    Betrifft: AW: Dynamische Formel aus VBA in Excel
    von: Markus
    Geschrieben am: 02.11.2020 14:45:27

    Genial, ich glaube ich habe es geschafft... ist zwar ev. nicht die beste Möglichkeit, aber was solls.
    Fast selbst geschafft und stolz drauf :)
    Danke für die Hilfe Daniel, du hast mich in die richtige Richtung geschubst.



    Beiträge aus dem Excel-Forum zum Thema "Dynamische Formel aus VBA in Excel"