Office: Makro: Formel hinzufügen

Moin,

habe gerade ein kleines Problem mit meinem Makro.
Das Makro soll Einträge aus einem Tabellenblatt in eine Tabelle einfügen, soweit funktioniert alles. Jedoch möchte ich nicht nur die "nackten" Daten, sondern auch Formeln einfügen, da stellt sich mein Excel jedoch quer.
Habe ich eine falsche Syntax angewendet?

Hier mal das Makro:

Code:

Sub Pyrostat_1_Kalk_Add()
'''''''''''''''''''''''''''''''''''''''''''
'Fügt der Einzelkalkulation Pyro hinzu'
'''''''''''''''''''''''''''''''''''''''''''
Dim b_artikel As Range
Dim b_bedarf As Range
Dim b_materialpreis As Range

Set b_artikel = Worksheets("Pyro").Range("A4:E4")
Set b_bedarf = Worksheets("Pyro").Range("C23")
Set b_materialpreis = Worksheets("Pyro").Range("C29")

Dim neueZeile As Range, freieZeile As Integer
Set neueZeile = Range("I5:I17").Find("")

freieZeile = neueZeile.Row
Cells(freieZeile, 9).Value = (b_artikel)
Cells(freieZeile, 10).Formula = "=SVERWEIS(A4;Material!A6:Material!E200;2;0)"
Cells(freieZeile, 11).Value = (b_bedarf)
Cells(freieZeile, 13).Value = (b_materialpreis)

edit: Fehlermeldung: Laufzeitfehler 1004

 

falls Du die Formel und das Format übernehmen willst musst Du den Bereich kopieren.

 

Das Format spielt in dem Fall keine Rolle.
Er soll ja lediglich in die neue Tabelle die SVERWEIS Funktion einfügen, damit die restlichen benötigten Daten (Einheit, Einzelverkaufspreis) aus einem anderem Tabellenblatt eingefügt werden.
Wenn ich die SVERWEIS Funktion ohne "=" eingebe fügt er mir das Ganze in die Tabelle ein, bloß ohne das "=" Funktioniert die Funktion nicht :-?

Wie meinst du das mit dem "kopieren" ?
soll ich die Formel in eine andere (unsichtbare) Zelle eintragen und diese dann in die Tabelle kopieren?

 

bei "=SVERWEIS(A4;Material!A6:Material!E200;2;0)"
musst Du ; durch , ersetzen es ist englisch

 

Ohh Danke, jetzt fügt er die Formel so ein wie ich sie haben möchte.

Jetzt zeigt er mir in der entsprechenden Zelle jedoch an "#NAME?" an:-?

Wenn ich jedoch in die Fnktion-bearbeiten-Leiste gehe und mit der Maus hinter die Funktion klicke und bestätige gibt er mir in der Zelle den exakten Wert an. die Funktion funktioniert also. Warum dann die Fehlermeldung?

mfg

 

Du hast .Formula also Formel in englisch, Du schreibst aber SVERWEIS() Deutsche Formel.
Falls Nur für Deutschland .FormulaLocal

 

Das wars :-D

habe jetzt anstelle von FormulaLocal einfach SVERWEIS in VLOOKUP (engl. SVERWEIS) geändert. Beim eintragen steht dann in der Zelle wieder SVERWEIS

 

Den hier mag er nicht, oder?

2 Möglichkeiten:

a) Wenn Deine Mappe garantiert nicht in anderen Sprach-Regionen benutzt wird, kannst Du ".formula" auf ".formulalocal" ändern. Damit akzeptiert Excel eine Formel per VBA auch in der "lokalen" Sprache". ich würde aber grundsätzlich davon abraten.

b) Du mußt die Formel in die "Excel-native" Sprache umwandeln, was Englisch wäre. Aus dem "sverweis" wird dann ein "vlookup" und die ";" wechseln in ein ",".

Code:

Cells(freieZeile, 10).Formula = "=vlookup(A4,Material!A6:Material!E200,2,0)"

2. Problem: Du hast diese Formel dann in der Zeile "freieZeile", prüftst in dieser Formel aber immer den Wert aus Zeile "4" ab. Ich denke, das ist nicht so gewollt, oder? Vielleicht ist besser:

Code:

Cells(freieZeile, 10).Formula = "=vlookup(A" & freiezeile & ",Material!A6:Material!E200,2,0)"

Gruß, Michael

 

das mit dem A4 stimmt schon, kann man aber ohne die Originaldatei zusehen nicht wissen. Das Makro sammelt eben Daten aus einer Kalkulation und fasst diese in einer Tabelle zusammen, sachen wie Preise etc. bezieht es aus einer seperaten Datenbank. Also 3 Datenblätter im Endeffekt.

Aso, bevor ich ein neues Thema eröffne noch 2 kleine Fragen am Rande.

Habe es leider nirgends gefunden, wie lautet der Befehl um einer Tabelle eine neue Zeile anzufügen?
Möchte das ganze etwas "dynamischer" machen sodass die Tabelle um 1 Zeile ergänzt wird sobald die Tabelle nur noch eine Zeile hat.

Und die zweite Frage.
Würde beim aktivieren des Makros gerne die Daten in eine Tabelle auf einem anderem Tabellenblatt übertragen.
Bis jetzt mache ich das mit

Code:

Sheets("Gesamtbedarf").Select
+ Restquelltext

Geht es in der Hinsicht evtl einfacher?
Wenn ich z.B.

Code:

Dim neueZeile As Range
Set neueZeile = Worksheets("Anderes_Tabellenblatt").Range("B4:B17").Find("")

freieZeile5 = neueZeile5.Row
Cells(freieZeile, 1).Value = "Text"

schreibe, müsste er ja eignetlich Wissen wo die Werte hinkommen, da "neueZeile" als Zelle in dem anderen Tabellenblatt dimensioniert worden ist.

 

Mit

Code:

worksheets(sht).rows(y).insert

kannst Du eine komplette, leere Zeile einfügen. Willst Du gleich die Formeln aus der z.B. direkt darüber liegenden Zeile übernehmen, dann wäre danach noch .copy und .paste (oder .pastespecial) angesagt.

Welche genau?

Wohin genau?

Fehlt da irgendwas, ist da was beim copy&paste verloren gegangen? Damit überträgst Du nichts, sondern markierst nur einen Bereich...

Und hier weißt du über die Variable "freieZeile5", wo sich die erste leere Zeile befindet. Dorthin kannst Du Deine Werte kopieren, ja.

Wenn ich Dich richtig verstehe, worüber ich mir aber nicht ganz sicher bin, könntest Du mit

Code:

worksheets(sht1).rows(freiezeile).copy destination:=worksheets(sht2).rows(freiezeile5)

den Kram kopieren, wobei sht1="Pyro" und sht2="Anderes_Tabellenblatt" sein müßten...

Gruß, Michael

 

danke schonmal für die Antwort, hat mir geholfen das ganze etwas zu vereinfachen, damit wird das selektieren eines anderen Tabellenblattes überflüssig weil ich direkt einfügen kann (in eine freie Zeile) :-)

Zu der anderen Sache,
mit:

Code:

worksheets(sht).rows(y).insert

dürfte das nicht wirklich funktionieren, da ich die Zeile doch in das Tabellenblatt und nicht in die Tabelle auf dem Tabellenblatt einfüge.
Habe auf dem Tabellenblatt 3-4 Tabellen denen ich nach Bedarf eben eine neue Zeile hinzufüge.

edit: Ist es möglich per Makro Tabellen direkt anzusprechen? (Um evtl Eigenschaften oder Zeilen festzulegen?)

 

Hmmm... Worin siehst Du den Unterschied? Die Worte "Tabelle" und "Tabellenblatt" kann man in Excel durchaus synonym verwenden. Ein Tabellenblatt besteht aus, ist, stellt dar: eine Tabelle. ;-)

Wenn Du allerdings z.B. folgende Situation hast:
Im Bereich B3:D8 hast Du einen Datenbereich, den man als "Tabelle auf dem Tabellenblatt" bezeichnen könnte. Du möchtest vor Zeile 6 eine Zeile einfügen, aber nur in diesen "Teilbereich"? Die Zeilen 6..8 sollen dabei nach unten verschoben werden? Je nachdem, was dann mit der "alten" Zeile 8 passieren soll, gibt es 2 Möglichkeiten:

a) Wenn Zeile 8 in Zeile 9 runter rutschen soll, dann geht es weiterhin mit einem "insert", allerdings dann nicht über die gesamte Zeile des Blatts ("rows(..."), sondern nur über den Bereich B..D der Zeile ("range(...").

b) Wenn Zeile 8 verschwinden soll, Zeile 9 also frei bleiben muß, dann geht es über eine Kombination von copy, paste (bzw. copy mit parameter destination) und clear.

Yups... Mit "range(..." oder "cells(...)" sprichst Du ja einen Bereich an. Gibst Du davor nichts explizit an, wird das "aktive" Tabellenblatt genommen. Damit wären "cells(3,5)" und "activesheet.cells(3,5)" synonym.

Möchtest Du einen Bereich eines anderen Tabellenblatts ansprechen, kannst Du das mit der "worksheets"-Auflistung erreichen. Möchtest Du also auf dem Blatt "Zusammenfassung" etwas verändern, egal, welches Blatt gerade das aktive ist, dann kannst Du das mit

Code:

worksheets("Zusammenfassung").cells(...

erreichen.

Du kannst sogar bei mehreren geöffneten Arbeitsmappen quer Beet über die Mappen Bereiche ansprechen:

Code:

workbooks("Auswertung").worksheets("Zusammenfassung").cells(...

Der Makro-Recorder geht dabei den schnarchlahmen Weg:
Mappe aktivieren
Blatt aktivieren
Zelle / Bereich selektieren
Selektion bearbeiten

Und das ganze läßt sich eben vermeiden, indem man den Bereich direkt, ohne Aktivierung, anspricht. Die nicht stattfindende (weil nicht notwendige, weil im Hintergrund) Aktualisierung des Bildschirms beschleunigt den Ablauf dann nämlich kolossal...

Gruß, Michael