Rechnung erstellen mit gleichzeitiger Aktualisierung der Bestandsliste

Liebe Forenmitglieder,


ich bin neu hier im Forum, mit wenig Excel-Wissen behaftet, hatte bis zu meiner Pensionierung nie etwas mit Excel zu tun gehabt und habe nun ein riesiges Problem.

Ich habe versucht, für eine Freundin mit Excel ein Formular "Lieferschein und Rechnung" zu kreieren.

Im Netz habe ich viele Anregungen gefunden, aber an einem Punkt verzweifele ich, wahrscheinlich liegt es an meinen stümperhaften Excel-Kenntnissen, aber das hilft mir auch nicht wirklich weiter.

Mein Problem:
Welche Formel muss ich in Zelle D7 des Tabellenblattes "Bestand" eingeben, damit Eingaben in den Zellen A23 und E23 des Tabellenblattes "Rechnung" dazu führen, dass in Zelle D7 des Tabellenblattes "Bestand" der Bestand des Artikels automatisch aktualisiert wird?

Ist jemand willens und in der Lage, hier hilfreich einzuspringen?

Ich bedanke mich auf jeden Fall für Eure Mühe.


Lothar X

 

Das automatsiche Aktualisieren macht Excel selber. Die (Gegen-)Frage ist aber wie sollen denn die Werte in A23 und E23 verknüpft werden? Sollen die Werte addiert werden? Dann heißt die Formel: "=A23+E23".

Das musst du schon sagen.

 

Hallo Mike,
ich habe versucht, das Excel Tabellenblatt dem Artikel beizufügen, aber da stosse ich mit meinen Kenntnissen wohl an die Grenzen, ich weiss einfach nicht, wie es funktioniert, vielleicht bin ich auch einfach nur zu **** dafür ;-).
LotharX

 

Ohne Kommentar. Ich habe das noch nie versucht, glaube, das geht hier auch gar nicht.

 

Hallo Mike,
Kannst Du mir vielleicht mitteilen, wie ich die dazu gehörende Tabelle hochladen kann, dann ist vielleicht einiges einfacher zu klären?
LotharX

 

Bitte auch lesen was ich schreibe!!!

 

@LotharX

Mach Screenshots von den beiden Tabellenblättern und lade diese hier hoch als Bild.
>> Printscreen-Taste drücken, dann in Paint einfügen, als *.PNG speichern und hier als Anhang hochladen.

Gruss Urs

 

Hallo Urs,
hier sind die beiden Tabellenblätter. Ich hoffe, Du kannst alles Notwendige daraus lesen.
Gruss
Lothar

 

In A23 steht nichts drin. Was wird denn dort eingegeben?

Wie ich schon schrieb, aktuallisieren tut Excel allein.

 

Die Zelle A 23 beinhaltet eine Liste (Daten -> Gültigkeit), die die einzelnen Artikelnummern beinhaltet. Bei Auswahl einer Artikelnummer wird dann automatisch in den Zellen B 23 bis D 23 die Artikelbeschreibung angezeigt, in E 23 die Anzahl, in F 23 der Preis pro Stück, in H 23 der Preis, in H 44 der Betrag und in GH 48 der Gesamtbetrag.

Die Artikelnummern sind folgendermassen aufgebaut:
Artikelnummer 1001 - Ausgabe in Zelle E 23 = 1 (Stück),
Artikelnummer 1002 - Ausgabe in Zelle E 23 = 2 (Stück),
Artikelnummer 1003 - Ausgabe in Zelle E 23 = 3 (Stück) ...
...
Artikelnummer 2001 - Ausgabe in Zelle E 23 = 1 (Stück),
Artikelnummer 2002 - Ausgabe in Zelle E 23 = 2 (Stück) usw.

Was mir nun fehlt, ist eine Formel in dem Blatt Bestand (in Zelle D 7), die automatisch die Zahl in Zelle E 23 auf dem Rechnungsblatt (Zahl der versandten Artikel) mit dem Bestand verrechnet, so dass dann der noch verfügbare Bestand angezeigt wird.

Ich hoffe, ich habe mich verständlich ausgedrückt!

 

@LotharX

Ich verstehe die Funktionen so (korrigiere mich ggf. bei jedem Punkt):

1. Im Blatt "Rechnung" wird die benötigte Anzahl Zeilen über den Dropdown gefüllt.
Der Kunde kann nur die mit der Artikelnummer schon vorgegebene Anzahl kaufen?

2. Von Hand werden die Kundendaten eingegeben/abgerufen. Preisberechnung automatisch mit Formel.

3. Das Steuerelement "Rechnungsnummer hinzufügen und 2 x drucken" ist ein Makro und macht das, was es sagt (plus Datum einfügen).
Anschliessend löscht es die variablen Eingaben Kunde, 2 Nummern, Datum und die Zeilen mit den Artikeln.
Bereit für eine neue Rechnung...

4. Ein gedrucktes Exemplar wird versandt, eines geht in die Buchhaltung.

5. In der Arbeitsmappe selbst bleibt nichts übrig von der Rechnung.

6. >>> Der Lagerbestand soll nachgeführt werden.

Wenn meine Annahme einigermassen stimmt, dürfen im Blatt "Bestand" die Zellen D7 usw nur Zahlen, aber keine Formel enthalten. Sie dürfen den Bestands-Abgang nicht selbst beziehen, er muss ihnen gebracht werden!
Sonst holen sie jede Aenderung im Rechnungsblatt, und damit auch das Zurücksetzen...

Es gibt nur einen Zeitpunkt an dem der Bestand nachgeführt werden darf > Wenn die Rechnung ausgedruckt und im Arbeitsblatt gelöscht wird.
Das lässt sich in das schon vorhandene Makro einbauen!

Habe ich das so richtig gesehen?

Gruss Urs

 

@Urs2

Danke erst einmal für Deine Mühe, nun zu den einzelnen Punkten:

zu 1. und 2.:

ja. Die Artikelnummer ist 5stellig, davon bezeichnen die ersten 4 Stellen den Artikel, die 5. Stelle die Anzahl des jeweiligen Artikels.
Bei der Artikeleingabe z. B. 10011 in A 23 (Artikel) durch Auswahl aus den Vorgaben des Dropdownfeldes wird automatisch die gesamte Zeile ausgefüllt. Dabei sind den einzelnen Zellen folgende Formeln unterlegt:
B 23: (Beschreibung) =WENN(ISTZAHL(A23);SVERWEIS($A23;$R$3:$U$126;2;FALSCH);"")
E 23: (Anzahl) =WENN(ISTZAHL(A23);SVERWEIS(A23;$R$3:$U$126;3;FALSCH);"")
F 23: (Preis/Stück) =WENN(ISTZAHL(A23);SVERWEIS(A23;$R$3:$U$126;4;FALSCH);"")
H 23: (Preis) =WENN(ISTZAHL(E23);E23*F23;"")
H 44: (Betrag) =WENN(ISTZAHL(H23);SUMME(H23:H43);"")
H 48 (Rechnungsbetrag) =WENN(ISTZAHL(H23);SUMME(H23:H43);"")

Bestehende Kundendaten werden in das Blatt "Rechnung" durch Eingabe des Kundennamens in Feld K 23 "Name" in der Eingabeliste "Kundennummer suchen, Einen Namen eingeben, Enter drücken" übernommen.
Dabei sind den folgende Zellen Formeln unterlegt:
K 10: (Kundennummer) =WENN(ISTFEHLER(INDEX(Kunden!B2:B1000;VERGLEICH(K9;Kunden!B:B;0);1));"";INDEX(Kunden!A:A;VERGLEICH(K9;Kunden!B:B;0);1))
H 13: (Kundennummer) =K10
A 10: (Name) =WENN(ISTFEHLER(SVERWEIS($H$13;Kunden!$A:$F;2;0));"";SVERWEIS($H$13;Kunden!$A:$F;2;0))
A 11: Vorname) =WENN(ISTFEHLER(SVERWEIS($H$13;Kunden!$A:$F;3;0));"";SVERWEIS($H$13;Kunden!$A:$F;3;0))
A 12: (Strasse und Hausnummer) =WENN(ISTFEHLER(SVERWEIS($H$13;Kunden!$A:$F;4;0));"";SVERWEIS($H$13;Kunden!$A:$F;4;0))
A 14: (PLZ) =WENN(ISTFEHLER(SVERWEIS($H$13;Kunden!$A:$F;5;0));"";SVERWEIS($H$13;Kunden!$A:$F;5;0))
B 14: (Ort) =WENN(ISTFEHLER(SVERWEIS($H$13;Kunden!$A:$F;6;0));"";SVERWEIS($H$13;Kunden!$A:$F;6;0))


zu 3.:

Die Kundendaten werden in das Blatt "Kunden" eingegeben über die Schaltfläche "Einen neuen Kunden hinzufügen", dann öffnet sich per Makro das Blatt "Kunden" und eine Eingabemaske mit den benötigten Kundendaten erscheint.

Das Steuerelement "Rechnungsnummer hinzufügen und 2 x drucken" ist ebenfalls ein Makro und macht das, was es sagt,
das Datum wird in Feld H 16 durch die Formel =HEUTE() erzeugt.

zu 4., 5. und 6.:

alles korrekt!

Das Makro in der Schaltfläche "Rechnungsnummer hinzufügen und 2 x drucken" sieht bei mir so aus (ich bin leider nur Laie und habe versucht, es zusammenzubasteln):

Sub RechnungsnrUndDrucken()
Sheets("Rechnung").Select
[H14] = [H14] + 1 'Rechnungsnummer 1 hochzählen
ActiveWindow.SelectedSheets.PrintOut _
Copies:=2, Collate:=True 'Drucken
End Sub

Funktionieren tut es, aber ich weiss nicht, wie ich es anstellen soll, dass die in der Rechnung genannte Anzahl des Artikels (Zelle E 23) von dem Bestand in dem Blatt "Bestand" in Zelle D 7) automatisch abgezogen wird.

Gruss

Lothar

 

@Lothar

Alles klar, kein Problem in Sicht, aber leider muss ich jetzt gleich weg...
Ich habe Excel2003. Und Du?

Heute Nacht werde ich einen Erweiterungsvorschlag für das Makro ausbrüten...

Gruss Urs

 

@Urs2

Ich habe Excel 2002 (10.4302.4219) SP-2.

Gruss

Lothar

 

...die sind ja nahe verwandt. Ich würde aber trotzdem bei MicrosoftUpdate einmal nach dem Office2002 SP3 schauen...

Druckst Du mit Deinem Makro nur den Rechnungs-Bereich? Den Rest des Blattes willst Du doch nicht ausdrucken?
Vermutlich ein aufgezeichnetes Makro, das auf die damals vorhandene Druckeinstellung zurückgriff. Wenn jemand etwas anderes drucken sollte, ist diese Einstellung dann weg...
Ggf. diesen Teil des Makros neu aufzeichnen und dabei alles nachvollziehen > Arbeitsblattwahl, Druckbereich auswählen, nur Bereich drucken, Seite anpassen, Anzahl...

Unabhängig vom Vorhergehenden müsste diese Ergänzung beim Drucken den Lagerbestand nachführen >>>

Code:

Sub RechnungsnrUndDrucken()
Dim intArtikel
Dim intMenge
Dim intBestand

Sheets("Rechnung").Select
[H14] = [H14] + 1 'Rechnungsnummer 1 hochzählen
ActiveWindow.SelectedSheets.PrintOut _
Copies:=2, Collate:=True 'Drucken

                        'wählt oberste Rechnungszeile
Worksheets("Rechnung").Range("A23").Select
Do
                        'wenn ArtikelNummerZelle leer > Exit
    If ActiveCell.Value = "" Then Exit Do
                        'registriert ArtNummer
    intArtikel = ActiveCell.Value
                        'registriert Verkaufsmenge
    intMenge = ActiveCell.Offset(0, 4)
                        'registriert Bestand
    intBestand = Worksheets("Bestand").Range("B7:B37").Find(intArtikel).Offset(0, 2)
                        'zieht Verkauf von Bestand ab
    Worksheets("Bestand").Range("B7:B37").Find(intArtikel).Offset(0, 2).Value = intBestand - intMenge
                        'geht eine Zeile tiefer in Rechnung
    ActiveCell.Offset(1, 0).Select
Loop

End Sub

Gruss Urs


PS: Hier wurde ein Makro mit Bereich aufgezeichnet > http://www.pcwelt.de/forum/office-programme-microsoft-office-openoffice-org-staroffice/311556-druck-makro-f-r-excel.html#post1719058

 

@Urs2

Ich habe mein Makro durch Deines ersetzt, den "Lieferschein und Rechnung" vollständig ausgefüllt und durch Zelleneingabe in A 23 : A 43 (Dropdownliste) verschiedene Artikel eingegeben, dann die Schaltfläche "Rechnungsnummer hinzufügen und 2 x drucken" gedrückt.

Lieferschein und Rechnung wurden 2 x richtig ausgedruckt, aber im Blatt "Bestand" hat sich gar nichts getan.

Es erschien eine Fehlermeldung "Microsoft Visual Basic Laufzeitfehler '91': Objektvariable oder With-Blockvariable nicht festgelegt."

Gleichzeitig war in Deinem Makro die Zeile
"intBestand = Worksheets("Bestand").Range("B7:B37").Find(intArtikel).Offset(0, 2)"
gelb unterlegt.

Habe ich etwas vergessen, das ich hätte abändern oder hinzufügen sollen?

Dein Hinweis auf die Aufzeichnung des anderen Makros hilft mir auch nicht weiter, da ich nicht weiss, wie ich hier durch markieren oder anklicken vorgehen soll.

Gruss

Lothar

 

@Lothar >>> das überrascht mich jetzt...

Ich hatte das Makro vorher in meinem Excel2003 mit Deinen Blattnamen und Deinen Zellen getestet > OK

Vielleicht ist Excel2002 eben doch anders, dann werden wir einen anderen Weg suchen. Aber vorher >

- Hast Du Office2002 schon auf SP3 upgedatet? Da sind vielleicht auch für VBA Aenderungen drin.

- Mein VB-Editor zeigt unter Menü ? > Info die Version 6.5 an. Bei Dir?

- Im Menu > Extras > Verweise sind bei mir folgende Zeilen aktiviert >
> Visual Basic for Applications
> OLE Automation
> Microsoft Office 11.0 Object Library
> Microsoft Excel 11.0 Object Library
>>> 11.0 ist Office/Excel2003, bei Dir heisst das vielleicht 10.0
...und bei Dir?

Gruss Urs

 

@Urs

Nein, ich habe noch nicht auf SP 3 upgedatet.

Mein VB-Editor zeigt unter Menü ? > Info die Version 6.0 an.

Im Menu > Extras > Verweise sind bei mir folgende Zeilen aktiviert
> Visual Basic for Applications
> OLE Automation
> Microsoft Office 10.0 Object Library
> Microsoft Excel 10.0 Object Library

Ich weiss jetzt nicht, ob es überhaupt möglich oder zu unverschämt ist, aber besteht hier die Möglichkeit, meine Tabelle hochzuladen oder per email als Anhang zu senden? Vielleicht ist ja dann leichter erkennbar, was ich falsch gemacht habe.

Gruss

Lothar

 

Ja natürlich, wenn die Datei <100KB ist, hänge einfach ein TXT dran, so > DeineDatei.XLS.TXT und an Beitrag anhängen.
Wenn Du meinen Code korrekt übertragen hattest, hilft mir das aber nichts, mein Excel2003 akzeptiert meinen Code...
Ich stochere gezwungenermassen etwas im Nebel...

Ich würde aber das Office zuerst einmal auf SP3 updaten, wer weiss schon, was da Gutes drin ist. Das SP3 ist ja von 2004, da war Excel2003 schon auf dem Markt...

Ich habe den Code einmal "auseinandergezogen", bei mir funktioniert er perfekt, vielleicht versteht das Excel2002 besser.

Code:

Sub RechnungsnrUndDrucken2()
Dim intArtikel
Dim intMenge
Dim intBestand

Sheets("Rechnung").Select
[H14] = [H14] + 1 'Rechnungsnummer 1 hochzählen
    '>>>>Drucken auskommentiert, zum Papier sparen bei Tests
'ActiveWindow.SelectedSheets.PrintOut _
Copies:=2, Collate:=True 'Drucken

                        'wählt oberste Rechnungszeile
Worksheets("Rechnung").Range("A23").Select
Do
                        'wenn Zelle leer Exit
    If ActiveCell.Value = "" Then Exit Do
                        'registriert ArtNummer
    intArtikel = ActiveCell.Value
                        'registriert Verkaufsmenge
    intMenge = ActiveCell.Offset(0, 4)
                        'registriert Bestand
    Worksheets("Bestand").Select
    ActiveSheet.Range("B7:B37").Select
    intBestand = Selection.Find(What:=intArtikel, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Offset(0, 2)
                        'zieht Verkauf von Bestand ab
    Selection.Find(What:=intArtikel, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Offset(0, 2).Value = intBestand - intMenge
                        'geht eine Zeile tiefer in Rechnung
    Worksheets("Rechnung").Select
    ActiveCell.Offset(1, 0).Select
Loop
End Sub

Vielleicht schaut Beverly hier vorbei. Sie ist die Spezialistin für hoffnungslose Fälle, und vor allem kennt sie die Eigenheiten der verschiedenen Excel-Versionen.

Beverly --- Bitte...

Gruss Urs

 

@Urs2

SP3 habe ich noch nicht installiert

Ich habe die Datei angehangen, gegenüber der vorherigen Version ist sie bis auf die Zellen Rechnungsnummer (vorher H 14, jetzt H 16) lediglich in den Bereichen geändert, denen keine Formeln hinterlegt sind.

Für die Aktualisierung des Bestandes (Blatt "Bestand") habe ich nun zwei Makros, die der Schaltfläche "Rechnungsnummer hinzufügen und 2 x drucken" unterlegt werden können,
1. RechnungsnrUndDrucken und
2. RechnungsnrUndDrucken2.

zu 1. (RechnungsnrUndDrucken):

Die Rechnung wird 2fach korrekt ausgedruckt.

Auf dem Blatt "Rechnung" erscheint ein Fenster mit der Überschrift "Microsoft Visual Basic" und dem Text " Laufzeitfehler '91': Objektvariable oder With-Blockvariable nicht festgelegt."
Die Schaltflächen "Beenden", "Debuggen" und "Hilfe" sind aktiviert.
Nach Anklicken der Schaltfläche "Debuggen" öffnet sich ein Fenster mit der Überschrift "Microsoft Visual Basic - Test PcWelt.xls [Unterbrechen],
darin ist ein weiteres Fenster mit der Überschrift "TestPcWelt.xls – Modul4 (Code)" mit folgendem Inhalt:

Sub RechnungsnrUndDrucken()
Dim intArtikel
Dim intMenge
Dim intBestand

Sheets("Rechnung").Select
[H16] = [H16] + 1 'Rechnungsnummer 1 hochzählen
ActiveWindow.SelectedSheets.PrintOut _
Copies:=2, Collate:=True 'Drucken

'wählt oberste Rechnungszeile
Worksheets("Rechnung").Range("A23").Select
Do
'wenn ArtikelNummerZelle leer > Exit
If ActiveCell.Value = "" Then Exit Do
'registriert ArtNummer
intArtikel = ActiveCell.Value
'registriert Verkaufsmenge
intMenge = ActiveCell.Offset(0, 4)
'registriert Bestand
intBestand = Worksheets("Bestand").Range("B7:B37").Find(intArtikel).Offset(0, 2)
'zieht Verkauf von Bestand ab
Worksheets("Bestand").Range("B7:B37").Find(intArtikel).Offset(0, 2).Value = intBestand - intMenge
'geht eine Zeile tiefer in Rechnung
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Die Zeile " intBestand = Selection.Find(What:=intArtikel, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Offset(0, 2)" ist gelb unterlegt, und vor dieser Zeile befindet sich ein gelber Pfeil mit Ausrichtung nach rechts.

zu 2.( RechnungsnrUndDrucken2):

sofort nach Betätigen der Schaltfläche "Rechnungsnummer hinzufügen und 2 x drucken" (Die Rechnung wird nicht gedruckt.) erscheint auf dem Tabellenblatt "Bestand" ein Fenster mit der Überschrift "Microsoft Visual Basic" und dem Text " Laufzeitfehler '91': Objektvariable oder With-Blockvariable nicht festgelegt."
Die Schaltflächen "Beenden", "Debuggen" und "Hilfe" sind aktiviert.
Nach Anklicken der Schaltfläche "Debuggen" öffnet sich ein Fenster mit der Überschrift "Microsoft Visual Basic - Test PcWelt.xls [Unterbrechen],
darin ist ein weiteres Fenster mit der Überschrift "TestPcWelt.xls – Modul4 (Code)" mit folgendem Inhalt:

Sub RechnungsnrUndDrucken2()
Dim intArtikel
Dim intMenge
Dim intBestand

Sheets("Rechnung").Select
[H14] = [H14] + 1 'Rechnungsnummer 1 hochzählen
'>>>>Drucken auskommentiert, zum Papier sparen bei Tests
'ActiveWindow.SelectedSheets.PrintOut _
Copies:=2, Collate:=True 'Drucken

'wählt oberste Rechnungszeile
Worksheets("Rechnung").Range("A23").Select
Do
'wenn Zelle leer Exit
If ActiveCell.Value = "" Then Exit Do
'registriert ArtNummer
intArtikel = ActiveCell.Value
'registriert Verkaufsmenge
intMenge = ActiveCell.Offset(0, 4)
'registriert Bestand
Worksheets("Bestand").Select
ActiveSheet.Range("B7:B37").Select
intBestand = Selection.Find(What:=intArtikel, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Offset(0, 2)
'zieht Verkauf von Bestand ab
Selection.Find(What:=intArtikel, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Offset(0, 2).Value = intBestand - intMenge
'geht eine Zeile tiefer in Rechnung
Worksheets("Rechnung").Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Die Zeile " intBestand = Selection.Find(What:=intArtikel, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Offset(0, 2)" ist gelb unterlegt, und vor dieser Zeile befindet sich ein gelber Pfeil mit Ausrichtung nach rechts.

Gruss

Lothar