Supportnet / Forum / Tabellenkalkulation

Hallo, habe eine Tabelle mit ein paar Spalten. In den Spalten steht jeweils die Artikelnummer, Bezeichnung und der Preis pro Artikel. In einer weiteren Spalte gebe ich dann ein Bestelldatum, die Anzahl der Artikel pro Bestellung an und lass in der nächsten Spalte den Gesamtbestellwert errechnen. Wenn die Bestellung dann eingegangen ist, lösche ich die Bestellung wieder heraus. Jetzt suche ich nach einer Möglichkeit die Bestellwerte übers Jahr zu kumulieren, so das ich am Ende des Jahres weiss wieviel Bestellkosten pro Artikel ich habe. Dieses sollte automatisch funktionieren sobald ich eine neue Bestellung mit Bestelldatum eingebe. Kann mir irgendjemand einen Ansatz sagen, wie ich es in VBA bewerkstelligen kann, denn ich denke rein mit Excel wird es nicht gehen. Gruss Holly

N´abend Holly,

Vorschlag:
Lass die Artikelübersicht z.B. in Blatt1 und gib die Bestellungen mit allen bisherigen Formeln (natürlich mit neuen Bezügen) in Blatt2 ein.
Dort kannst Du alles so belassen und hast immer alle erledigten Bestellungen im Blick.
Problem könnte sein, dass sich im Laufe des Jahres Deine ArtikelDB ändert und dann den erledigten Bestellungen neue Werte zugewiesen würden.
Kann man aber abhelfen, indem vor Änderungen in Tab1 alle Werte in Tab2 gefixt werden (von Formel auf Festwert).


MfG
schnallgonz

_{Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

Hallo Schnallgonz,

erstmal Danke für deine schnelle Antwort, allerdings komme ich damit nicht ganz klar. Ich hätte dann ein 2.Tabellenblatt wo ich meine Bestellungen drin habe, aber ich habe doch noch immer nicht die kumulierten Werte für die Bestellungen über das Jahr oder? Denn das ist mein großes Problem, wie ich Bestellwerte kumulieren kann, obwohl die Bestellwerte der einzelnen Bestellungen immer wieder rausgelöscht werden.

Gruss Holly

Moin Holly,

warum wilst Du in der Tab2 die Bestellwerte löschen?
Lass die Dinger einfach stehen.
Eine normale Summenformel drunter, fertig, oder verstehe ich das etwas falsch?

MfG
schnallgonz

_{Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

Mir ist gerade ein Grund eingefallen , warum Du ein Problem sehen könntest.

Du gibst in Blatt2 die ArtikelNr ein, Beschreibung und Preis holst Du mit SVERWEIS.
Daneben Eingabe von Datum und Anzahl, Summenformel, fertig.
Jahreswerte addierst Du ebenfalls mit einer simplen Summenformel.
Alles klaro?
Wenn SVERWEIS nicht zu Deinen guten Bekannten gehört,
kannst Du hier eine wunderbare Erklärung runterladen.

MfG
schnallgonz

_{Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

Moin Schnallgonz,

du hast Recht, bin kein großer Held ins Sachen Sverweis, allerdings habe ich das schon in der Datei hingebracht. Trotzdem weiß ich immer noch nicht wie das mit dem kumulieren funktioniert. Ich habe im ersten Tabellenblatt: Spalte A: Artikelnummer, Spalte B: Bezeichnung, Spalte C: Preis/Stück, Spalte D: Bestand, Spalte E: Bestellmenge, Spalte F: Preis/Bestellung. Wenn ich jetzt etwas entnehme, ziehe ich das vom Bestand ab. Wenn der Bestand eine gewisse Menge unterschritten hat, mache ich einfach eine Bestellung indem ich in die Spalte E meine gewünschte Menge eingebe und dann wird im Tabellenblatt 2 mit Sverweis die Bestellmenge, der Bestellpreis komplett und alle anderen Daten übertragen und ich lass es ausdrucken. Wenn die Bestellung jetzt eingegangen ist, trage ich es einfach beim Bestand ein und lösche die Zelle "Bestellmenge", damit ich weiß das keine offene Bestellung mehr läuft. Wenn ich allerdings die Bestellmenge wieder lösche ist natürlich auch der Bestellpreis komplett weg und ich habe nichts mehr zum kumulieren. Wie kann ich jetzt diesen Bestellpreis jetzt in eine Zelle übergeben, ohne das er abhängig ist von der Zelle "Bestellmenge"?

Gruss Holly

Ich glaube, bei mir ist erst jetzt der Groschen gefallen...

Mit VBA fallen mir spontan 2 Lösungen ein.

Du markierst die Bestellung in Tab1 und ein Makro überträgt die Markierung als Festwert in Tab2 in die nächste freie Zeile einer festgelegten Spalte

Du läßt die Formeln in Tab2 wie sie sind und ein Makro wandelt den Formelwert für den Bestellpreis in einen FestwertFür den jeweiligen Makroaufruf weist man dem Code einem Button zu.
Habe ich die Vorschläge verständlich beschrieben?
Wenn das was für Dich ist, melde Dich nochmal.
Dann bastel ich das entsprechende Listing.
Dazu wären ein paar Positionsangaben für Tab2 wichtig.

Bis dann
schnallgonz

_{Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

Hallo Schnallgonz,

ja ich denke mit der 2.Lösung müsste es klappen. Jetzt bräuchte ich eigentlich nur den Code um den Bestellwert in einen Festwert umzuwandeln und später dann die nächsten Bestellwerte aufaddieren zu können. Wäre es möglich diese Aufaddierung automatisch maschen zu lassen, jedesmal wenn ich in der Bestellmenge einen neuen Wert eingebe? Leider habe ich für Tab2 noch keine Positionsangaben, da ich ja bisher nicht weiß wie das kumulieren funktioniert. Mir würde es eigentlich schon reichen, wenn du mir einen Beispielcode nennen könntest, den werde ich dann versuchen so anzupassen, das es auf meine Tabellen zutrifft. Da ich die ganze Tabelle sowieso nochmal komplett machen wollte.

Gruss Holly

Hi,

ich glaube, heute stehe ich echt auf der Leitung...

Ich dachte bei Lösung2, dass Du in Tab2 praktisch alle Bestellungen des Jahres quasi als Protokoll listest .
Der Bestellpreis wird per Makro in einen Festwert umgewandelt.
Summenformel, fertig.
Wenn Dich die Summen je Artikel interessieren, kann man das wunderbar mit DBSUMME lösen.
Hier erstmal der Code für den Festwert.
(Bestellpreise markieren, die in Festwert umgewandelt werden sollen)

Sub BestellpreisFormelFestwert()
  Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
 Application.CutCopyMode = False
End Sub

Jetzt nehme ich aber an, dass Du nur den jeweiligen Bestellpreis in einer Extrazelle addieren willst?
Wenn das richtig ist, wäre Folgendes möglich:.
Beispiel für Deine Tab1:
In Spalte F stehen die Bestellpreissummen, in Spalte E die bestellten Mengen, richtig?
Jedesmal, wenn Du in Spalte E einen Wert einträgst, wird die Summe aus Spalte F dem Wert in Spalte G hinzugerechnet.
Bevor ich jetzt Gehirnschmalz in diese Lösung stecke, frage ich lieber nochmal, ob das die Lösung für Dich ist und vor allen Dingen:
erstmal Mittag Essen
Bis später
MfG
schnallgonz

_{Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

Hallo Schnallgonz,

ich möchte dir erstmal danken das du dich da so reinhängst, aber irgendwie habe ich das Gefühl ich habe das Ganze schlecht beschrieben. Also fange ich nochmal von vorne an. Die Spalten habe ich dir ja vorher schon beschrieben, allerdings habe ich für jeden Artikel nur eine Zeile, so das ich wenn ich 10 Artikel habe auch nur 10 Zeilen habe und in der dem Artikel entsprechender Zeile dann in die Spalte "Bestellmenge" eben meine Bestellmenge eintrage.
Zum Beispiel Artikelnummer 5 = E5 (dort wird meine Bestellmenge die ich bestellen will eingetragen) und aus E5 und C5 wird dann F5 ausgerechnet. Wenn ich jetzt aber die Bestellmenge nach Eingang in E5 wieder lösche dann wird natürlich auch der Wert F5 wieder auf 0 gesetzt. Jetzt müsste ich eben diesen Wert vorher an irgendeine Zelle übergeben wo dieser erstmal feststehen bleibt bis zur nächsten Bestellung und diese dann wieder aufsummiert wird. Am liebsten wäre mir das wenn dies alles beim Eintragen einer neuen Bestellmenge automatisch gehen würde.


Gruss Holly

Hallo Holly,

bitte, manchmal dauerts eben länger, bis man sich versteht, insofern ist mein Name Programm :-)
Besser, man bringt zuerst den Sachverhalt auf die Reihe, als sinnlosen Code zu schreiben oder hinterherzuprogrammieren.

Ich glaube aber, jetzt hammers, mit meinem letzten Vorschlag

liege ich richtig.

In Spalte F trägst Du nichts ein, das besorgt ja eine Summenformel, habe ich geschnallt.
Deshalb ist meine Idee, dass in dem Moment , in dem Du in Spalte E die Bestellmenge einträgst, automatisch der in Spalte F errechnete Bestellgesamtpreis in Spalte G addiert wird.
Dann kannst Du anschliessend die Bestellmenge aus Spalte E und damit auch den Gesamtpreis in Spalte F löschen;
das ist insoweit ohne Auswirkung, weil in Spalte G ein Festwert steht und keine Formel.
Bei der Löschung wird nur 0 addiert, trägst Du eine neue Bestellmenge ein, wird sofort der nächste Gesamtpreis addiert.
Wenn Du dies einem detaillierten Bestellprotokoll in Tab2 vorziehst, brauchst Du eine Tab2 nicht mehr.

So, mal sehen, ob wir soweit klar sind.

MfG
schnallgonz

_{Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

Hallo Schnallgonz,

jawohl so war das Ganze gemeint. Kann ich also deinen Code für den Festwert nehmen?

Gruss Holly

Hallo,

der schon gepostete Code tauscht im markierten Bereich nur Formeln gegen Festwerte, hilft Dir so also noch nicht weiter.

Die Gesamtlösung ist schon etwas länger, aber in Arbeit.

MfG
schnallgonz

_{Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

So Holly,

habe fertig.
Nachfolgenden Code mußt Du als VBA-Projekt Deiner Tabelle1 anlegen.
Wie geht das?

mit ALT+F11in VBA-Umgebung

im linken oberen Fenster Doppelklick auf Deine Tabelle1

in das rechte große Fenster das Listing kopieren

zurück in Deine Tabelle1, speichern, ausprobieren

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Rem wenn Werteingabe in Spalte E dann WertSpalteC x WertSpalteE nach SpalteG,
Rem wobei alter Zellinhalt SpalteG addiert wird
Rem funzt so nur, wenn Markierung nach Drücken von Enter nach unten verschoben wird
Rem wenn dieser Standard nicht eingestellt ist, OffsetIndex ändern oder
Rem Einstellungen unter Optionen Bearbeiten ändern
Rem
Rem Wenn Ziel = Spalte 5 (also Spalte E)
If Target.Column = 5 Then
Rem Wert aus SpalteE holen, eine Zeile höher wegen verschobener Markierung
    i = ActiveCell.Offset(-1, 0).Value
Rem Wert aus SpalteC holen, eine Zeile höher wegen verschobener Markierung
    j = ActiveCell.Offset(-1, -2).Value
Rem isse klar, oder?
    i = i * j
Rem alten Wert aus SpalteG holen
    k = ActiveCell.Offset(-1, 2).Value
Rem neuen Wert in SpalteG schreiben
    ActiveCell.Offset(-1, 2).Value = i + k
End If
End Sub

Die Zeilen mit "Rem" sind nur Kommentarzeilen zur Erläuterung. Da habe ich auch geschrieben, dass als Standard die Verschiebung der Markierung bei ENTER nach unten eingestellt sein muß. Der Zeilenumbruch im SN ist nicht so wie er im Listing sein soll.
Sore bitte dafür, dass die Kommentare in einer Zeiel stehen.

Ich hoffe, das funzt .

MfG
schnallgonz

_{Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

N´abend Holly,

es geht auch etwas einfacher, unabhängig von dem Verschieben der Einfügemarke.
Sieht so aus:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Rem wenn Werteingabe in Spalte E dann WertSpalteC x WertSpalteE nach SpalteG,
Rem wobei alter Zellinhalt SpalteG addiert wird
Rem
Rem Wenn Ziel = Spalte 5 (also Spalte E)
If Target.Column = 5 Then
Rem Wert aus SpalteE holen
    i = Target.Offset(0, 0).Value
Rem Wert aus SpalteC holen
    j = Target.Offset(0, -2).Value
Rem isse klar, oder?
    i = i * j
Rem alten Wert aus SpalteG holen
    k = Target.Offset(0, 2).Value
Rem neuen Wert in SpalteG schreiben
    Target.Offset(0, 2).Value = i + k
End If
End Sub

MfG
schnallgonz

_{Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

Hallo Schnallgonz,

erstmal noch ein Riesen-Dankeschön für deine Hilfe und dein Bemühen. Werde es morgen mal probieren und melde mich dann wieder ob ich es hingebracht habe.


Gruss Holly

Hallo Schnallgonz,

habe deinen Code eingefügt und angepasst und er funktioniert super. Nochmals ein Riesendankeschön. Jetzt hätte ich noch ein kleines Anliegen. Ich habe ein Makro aufgezeichnet, welches die Daten für die Bestellung in Tabellenblatt 2 kopiert und dann Tabelle 2 sozusagen als "Bestellschein" ausdruckt. Ist es möglich das dieses automatisch geht sobald man in der Zelle Bestellmenge eine Eingabe macht?
Hier ist der Code den ich aufgezeichnet habe:
Range("B2,C2,D2,E2,F2,G2").Select
Range("G2").Activate
Selection.Copy
Sheets("Tabelle2").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Sheets("Tabelle1").Select
Range("I2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle2").Select
Range("C10").Select
ActiveSheet.Paste
Sheets("Tabelle1").Select
Range("L2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Tabelle2").Select
Range("C12").Select
ActiveSheet.Paste
Sheets("Tabelle2").Select
ActiveSheet.PrintOut
Dieser funktioniert natürlich nur für die 2.Zeile und deshalb müsste ich für alle Zeilen das Ganze immer wieder so machen. Es müsste doch einen enfacheren Weg geben.


Gruss Holly

Hallo Holly,

schön, dass es funzt.
Auch auf den VBA-Geschmack gekommen, wie?
Es geht einfacher, was die Anwendung betrifft, der Code dazu ist etwas komplizierter.
Grundsätzlich würde ich es der Übersichtlichkeit halber so machen:
Du brauchst ein Listing für ein Modul, das die Kopierarbeit und das Einfügen in Tab2 übernimmt.
Dieses Makro (z.B."BSchein") ruft Du dann mit dem schon erfolgreich eingebauten Ereignis auf, indem Du am Ende vor End If einfach "BSchein" ohne Anführungszeichen schreibst.

Beispiel für Werteingabe Spalte E, vorausgesetzt, Markierung wird nach ENTER nach unten verschoben:

Sub BSchein()
Rem in Spalte E eine Zeile nach oben, dortigen Wert kopieren
Sheets("Tabelle1").ActiveCell.Offset(-1, 0).Copy
Rem in "C4" einfügen
Sheets("Tabelle2").Cells(4, 3).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Das kopierst und änderst Du für die anderen Werte ab schreibst alles untereinander in das obige Modul, dessen Text immer mit End Sub aufhören muß.
Ausgangspunkt ist die Spalte E in Tab1.
mit ActiveCell.Offset(-1, 1).Copy kopierst Du den Wert aus der Spalte G, mit ActiveCell.Offset(-1, -1) aus Spalte D.
Im Zielbereich sieht´s ähnlich aus:
C4 = Cells(4, 3)

Bei Problemen nochmal melden.

MfG
schnallgonz

_{Fanatismus ist die Begeisterung der Beschränktheit.
Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

Hi Holly,

noch´n Tip:
B2:E2 kopierst Du im Hieb mit

Sheets("Tabelle1").Range.(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, -3)).Copy

MfG
schnallgonz

_{Fanatismus ist die Begeisterung der Beschränktheit.
Signatur und Textformatierungen wurden mitSNTool V1.1.12 erstellt}

Hi Schnallgonz,

habe den Code reinkopiert und ausprobiert, ich hätte ja fast gesagt das ist mehr als g........., Einfach genial.

Hab recht herzlichen Dank.

Woher weißt du das eígentlich alles, machst du das beruflich oder hast du dir alles selbst angeeignet?


Gruss Holly :-))))))

Moin Holly,

Danke für die Rückmeldung, freut mich, das es funzt und dass Du die Umsetzung alleine hinbekommen hast, find ich klasse.
Noch´n Tipp:
Wenn das nicht Deine letzte VBA-Anwendung sein sollte,
kommentiere Dein fertiges Makro ausführlich mit den
Rem-Zeilen oder Hochkomma anstatt Rem.
Dann weißt Du auch in 4 Wochen noch, wie der Code genau funzt.

Aus der Not heraus habe ich mich in VBA eingelesen:
es gab einfach zuviel stupide Arbeit mit Excelauswertungen,
da hatte ich keinen Bock drauf.
Also ein Buch geschnappt und angefangen, den Rest mit
learning by doing und natürlich mit diesem SuperForum,
mit dem man eigentlich alles hinbekommt, nicht nur VBA.
Neben den VBA-Künstlern gibt´s nämlich auch zahlreiche Formelkünstler.

MfG
schnallgonz

_{Ein Erfahrener ist besser als 10 Gelehrte.
Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

Moin Schnallgonz,

das mit den Kommentaren werd ich machen. Hast du einen Buchtipp für Anfänger (für mich)?

Das mit den Formelkünstlern kann ich nur bestätigen, Klaus (want2cu) hat mir mal eine Formel erstellt, da wäre ich nie im Leben drauf gekommen.


Gruss Holly

Moin Schnallgonz,

das mit den Kommantaren werde ich machen.

Hast du einen Buchtipp für Anfänger (für mich)?

Das mit den Formelkünstlern stimmt, der Klaus (want2cu) hat mir mal eine Formel erstellt, da wäre ich im ganzen Leben nicht drauf gekommen.

Aber das Beste hier im Forum ist, das einem wirklich geholfen wird, nicht nur so ein kurzer Kommentar wie "Thema wurde schon behandelt", sondern man bekommt hier wirklich Unterstützung.

Gruss Holly

Hi Holly,

guck mal hier
dort findest Du viele Makros und ein Buchtipp von want2cu unter Antwort 27

MfG
schnallgonz

_{Ein Erfahrener ist besser als 10 Gelehrte.
Signatur und Textformatierungen wurden mit SNTool V1.1.12 erstellt}

• Zellwerte in Excel speichern und für neue Rechnung verwenden
• Werte kumulieren mit Access Abfrage