Min Wert ausgeben in Abhängigkeit von einem Zellwe

[Thomas_Ramel]

Grpüezi Martina

ich habe ein Problem mit einer Stückliste. Ich habe eine
Stückliste in der in Spalte B die Artikelnummern stehen. Zu
diesen Artikelnummern stehen in der Spalte H die
Verpackungsmaterialien mit Materialnummern und in Spalte L
der Lagerbestand. Ich muss nun in einer anderen Liste in
Spalte K immer das Verpackungsmaterial mit dem kleinsten
Lagerbestand, welcher dann nur als Zahl in der Liste
erscheinen soll, zu jedem Fertigartikel auslesen.

Kann mir irgentjemand helfen, damit ich nicht immer händisch
zu jedem Fertigartikel die Verpackungsmaterialien prüfen und
dann immer den kleinsten Lagerbestand in meine Liste eintragen
muss.

Hmmm, warum soll/muss das mit einem VBA-Makro geschehen?

Ich denke dass genau dazu deie Pivot-Tabelle das Mittel der Wahl ist - schau dir dazu die folgende Mappe mal näher an, da habe ich dir eine solche eingefügt:

http://users.quick-line.ch/ramel/Demo-Daten/tr_Datei…

Mit freundlichen Grüssen

Thomas Ramel

• MVP für MS-Excel -

[smalbop]

Hallo Martina,

jetzt verstehe ich es, glaube ich.

Nur noch eine Frage zum Ablauf: Bekommst du die Verpackungsmaterial-Tabelle beigestellt und musst nur die Warenbewegungsprotokoll-Tabelle pflegen? Das heißt, dort SAP-Nummer in Sp. B eintragen und Spalte K füllt sich dann aufgrund der Daten aus der Verpackungsmaterial-Tabelle „von allein“? Das wäre in der Tat sehr einfach. Du musst dazu nur die nachfolgende Routine in deinen Tabellenblattcode „Warenbewegungsprotokoll“ einfügen. Das meiste sind Kommentare. Damit du es leicht selber anpassen kannst, habe ich für die Tabellennamen „Klarnamen“ verwendet. Das ganze ist „quick and dirty“ und sicher nicht laufzeitoptimiert oder superelegant, aber es funktioniert.

Gruß
smalbop

Private Sub Worksheet\_Change(ByVal Target As Range)
 'Target ist Zelle mit geänderter SAP-Nummer
 Dim i As Long 'Zählvariabe für Tabellenzeilen
 Dim j As Long 'Zählvariabe für Tabellenzeilen
 Dim Anz As Long 'Übergabevariable für Minimalbestand
 'Wenn im aktiven Blatt 2. Spalte (B) ab 8. Zeile verändert wird
 If Target.Column = 2 And Target.Row \> 7 Then
 'Tabelle Verpackungsmat, 2. Spalte (B) wird durchsucht
 'auf Übereinstimmung mit Target-Wert
 With Worksheets("Verpackungsmaterial")
 i = 1
 Do
 'Nächste Zeilennummer
 i = i + 1
 'Wenn Übereinstimmung
 If .Cells(i, 2) = Target Then
 'wird der Wert der 12. Spalte (L) der
 'Variablen Anz zugewiesen, sofern der in
 'Anz bereits vorhand. Wert nicht kleiner ist

 'Hat Anz noch den Wert Null (1. Schleife)
 'wird zunächst der erste Lagerbestand zugewiesen
 If Anz = 0 Then
 Anz = .Cells(i, 12)
 End If

 If .Cells(i, 12) Target Then
 Exit Do
 End If

 End If
 Loop
 End With
 'In aktiver Tabelle wird in der 9. Spalte rechts neben
 'Spalte B (=K) der gefundene Minimalwert Anz eingetragen
 Target.Offset(0, 9) = Anz
 End If
End Sub

[Datenhexe]

Hallo Martina,

jetzt verstehe ich es, glaube ich.

Nur noch eine Frage zum Ablauf: Bekommst du die
Verpackungsmaterial-Tabelle beigestellt und musst nur die
Warenbewegungsprotokoll-Tabelle pflegen? Das heißt, dort
SAP-Nummer in Sp. B eintragen und Spalte K füllt sich dann
aufgrund der Daten aus der Verpackungsmaterial-Tabelle „von
allein“? Das wäre in der Tat sehr einfach. Du musst dazu nur
die nachfolgende Routine in deinen Tabellenblattcode
„Warenbewegungsprotokoll“ einfügen. Das meiste sind
Kommentare. Damit du es leicht selber anpassen kannst, habe
ich für die Tabellennamen „Klarnamen“ verwendet. Das ganze ist
„quick and dirty“ und sicher nicht laufzeitoptimiert oder
superelegant, aber es funktioniert.

Gruß
smalbop

Private Sub Worksheet_Change(ByVal Target As Range)
'Target ist Zelle mit geänderter SAP-Nummer
Dim i As Long 'Zählvariabe für Tabellenzeilen
Dim j As Long 'Zählvariabe für Tabellenzeilen
Dim Anz As Long 'Übergabevariable für Minimalbestand
'Wenn im aktiven Blatt 2. Spalte (B) ab 8. Zeile verändert
wird
If Target.Column = 2 And Target.Row > 7 Then
'Tabelle Verpackungsmat, 2. Spalte (B) wird durchsucht
'auf Übereinstimmung mit Target-Wert
With Worksheets(„Verpackungsmaterial“)
i = 1
Do
'Nächste Zeilennummer
i = i + 1
'Wenn Übereinstimmung
If .Cells(i, 2) = Target Then
'wird der Wert der 12. Spalte (L) der
'Variablen Anz zugewiesen, sofern der in
'Anz bereits vorhand. Wert nicht kleiner ist

'Hat Anz noch den Wert Null (1. Schleife)
'wird zunächst der erste Lagerbestand zugewiesen
If Anz = 0 Then
Anz = .Cells(i, 12)
End If

If .Cells(i, 12) Target Then
Exit Do
End If

End If
Loop
End With
'In aktiver Tabelle wird in der 9. Spalte rechts neben
'Spalte B (=K) der gefundene Minimalwert Anz eingetragen
Target.Offset(0, 9) = Anz
End If
End Sub

Hallo smalbop,

erstmal vielen Dank für Deine Hilfe, aber ich glaube, ich bin zu blöd. Leider bringe ich die Routine nicht zum laufen. Ich habe es als neues Modul eingefügt, aber leider tut sich nichts. Bitte hilf mir auf die Sprünge, wahrscheinlich mache ich einen Denkfehler und bekomme es deshalb nicht zum laufen.

Ich habe übrigens das Warenbewegungsprotokoll als Tabelle fertig und muss nur jeden Monat die Verpackungsmaterialien ect…, die ich als Exceltabelle aus unserem Warenwirtschaftssytem ziehe, eintragen. Das Ganze wird, bis auf meine Anfrage hier, mittels eines Makros verarbeitet.

LG Martina

[smalbop]

Hallo Martina

…nicht als Modulcode einfügen, sondern in den Tabellenblattcode!

[Alt] + [F11] öffnet den VBA-Editor, darin in die Ansicht „Projektexplorer“ gehen, auf die Tabelle „Warenbewegungsprot.“ klicken und Code ins sich öffnende Fenster kopieren. (Die Module sind weiter unten im Explorer zu finden!) So funktioniert es jedenfalls noch bei Excel 2003.

Und da das Programm abstürzt, wenn die eingegebene SAP-Nummer in der anderen Tab. nicht vorkommt, z. B. wegen Tippfehler, kannst du noch hinter der Variablendeklaration die Zeile

On Error End

einbauen.

So, ich muss jetzt erst mal ins Schwimmbad und kann mich erst abends wieder melden.

Gruß
smalbop

[Datenhexe]

Hallo smalbop,

ich schon wieder. Ich hoffe, es war schön im Schwimmbad.

So etwas habe ich noch nie gemacht. Wie bekomme ich das Programm denn zum Laufen? Und wo genau muss ich On Error End einfügen. Wenn ich es nach der Variablendeklination einfüge, bekomme ich immer die Fehlermeldung: Fehler beim Kompilieren: Erwarte GoTo oder Resume.

LG Martina

[Reinhard_fb8cb7]

Hallo Martina,

ich schon wieder.

du darfst nettigkeit wird hier belohnt.

So etwas habe ich noch nie gemacht. Wie bekomme ich das
Programm denn zum Laufen? Und wo genau muss ich On Error End
einfügen. Wenn ich es nach der Variablendeklination einfüge,
bekomme ich immer die Fehlermeldung: Fehler beim Kompilieren:
Erwarte GoTo oder Resume.

Ich glaub, da war jemand schon geistig sehr im Schwimmbad bei der Person mit der er sich trifft *denk* *kicher*

Nein, On Error Goto End kannste vergessen.

Zumindest in Excel 2000 gibt es nur:
On Error GoTo Zeile
On Error Resume Next
On Error GoTo 0

Goto 0 bedeutet, der nächste Feler wird wieder „bestraft“.
Resume Next bedeutet, egal welcher Fehler, an der nächsten Codezeile geht es weiter.

Goto Zeile, wahrscheinlich war das gemeint, dann kann man anfangs des Codes schon schreiben On Error Goto End

Dann muß aber auch, meist unten um Code, stehen:

End

Irgendwie glaub ich ich habe dich evtl. noch mehr verwirrt.
Mal was anderes, Thomas hat dir doch eine Pivotlösung gezeigt, klappt das nicht oder willste das nicht?

Dann hätte ich eine Bitte, ich hab grad Probleme mit rapidshare und kann deine mappe nicht runterladen.
Magst du sie noch mal hochladen, mit dieser fileupload oder sonstiger Adresse?
Also die Mappe wo ich erbeten habe alles unwichtige auszublenden usw.
Sorry für Umstände die ich amche

Gruß
Reinhard

[Datenhexe]

Hallo Reinhard,

die vorgeschlagene Pivotlösung geht leider nicht, da ich das vorgegebene Layout verwenden muss. Die Tabellen stehen alle fest und die Daten werden aus HTML Dateien nur immer wieder neu eingelesen. Im Warenbewegungsprotokoll werden die meisten Daten über Formeln eingelesen. Ich kann es nur leider nicht bei der Verpackung. Da liegt ja mein Problem.

Ich habe die Datei noch einmal hochgeladen, und hoffe, Du kannst mir weiterhelfen. Eine Formel würde mir ja auch genügen. Ich kann es nur leider nicht…
Hier der Link:

http://www.file-upload.net/download-2838407/Datei-f-…

LG Martina

[smalbop]

Hallo Martina,

ich habe die von mir geänderte Datei mal hochgeladen:

http://www.file-upload.net/download-2839131/Datei-fu…

Die Fehlerauffangroutine habe ich auch gleich mit eingebaut, sie war natürlich gestern selbst ein Fehler, wenn auch nicht aus dem von Reinhard vermuteten Grund.

Falls ihr noch einen Fehler bemerkt - einfach melden.

Gruß
smalbop

[Datenhexe]

Hallo smalbop,

erst mal vielen Dank für die Mühe trotz Schwimmbadbesuches und wahrscheinlich anderweitigen Verpflichtungen .

Jetzt habe ich aber noch die entscheidende Frage, wir bekomme ich das Programm zum laufen, über Makro ausführen geht es ja nicht. Da ich, wie gesagt, noch nie mit so einem Programm gearbeitet habe, stehe ich jetzt etwas ratlos davor.

LG Martina

[Reinhard_fb8cb7]

Jetzt habe ich aber noch die entscheidende Frage, wir bekomme
ich das Programm zum laufen, über Makro ausführen geht es ja
nicht. Da ich, wie gesagt, noch nie mit so einem Programm
gearbeitet habe, stehe ich jetzt etwas ratlos davor.

Hallo Martina,

ich kann derzeit bei rapidshare nix runterladen, deshalb meldete ich mich nicht zur neuen mappe.

Das Makro ist ein Ereignis-Makro, d.h. es läuft automatisch los wenn das Ereignis Worksheet_Change in dem Blatt passiert.

Smalbop hat es ja im Code kommentiert:
'Wenn im aktiven Blatt 2. Spalte (B) ab 8. Zeile verändert wird

D.h. ändere mal was in B8:B655536…

Gruß
Reinhard

[Datenhexe]

Hallo Reinhard,

vielen Dank für die Erklärung, es funktioniert. Mein nächstes Problem ist nun, dass in der Tabelle „Warenbewegungsprotokoll“, in der was geändert werden muss, damit das Makro startet, leider nichts verändert wird, die Daten sind fest. Verändert werden nur die Daten in der Tabelle Verpackung. Kann man das automatische auf die Tabelle Verpackung umschreiben? Diese Daten werden immer aktualisiert und dann müssten sich auch die Daten in der Tabelle „Warenbewegungsprotokoll“ neu einlesen.

Ich habe Dir aber gestern abend die Datei noch einmal unter fileupload hochgeladen, damit Du sie ansehen kannst. Ich hoffe, Du kannst sie dort downloaden.

Ich weiß ja ich nerve etwas, aber es wäre mir wirklich eine große Hilfe, da ich die Daten jeden Monat neu aufbereiten muss.

Vielen Dank und lg
Martina

[smalbop]

Hallo Martina,

tja, darum legen Leute wie Reinhard und ich solchen Wert auf eine eindeutige und vollständige Beschreibung der Aufgabenstellung…

Welche Spalten und welche Zeilen darin werden in Verpackungsmat. händisch geändert?
Kann es vorkommen, dass zu den vorhandenen neue Artikelnummern dort hinzukommen? Ist das dann eine neue Zeile - oder mehrere auf einmal?
Kann es sein, dass die entsprechenden Artikelnummern im Warenbewegungsprotokoll dann noch nicht vorhanden sind?
Was soll das Programm dann machen?

Fragen über Fragen…

Gruß
smalbop

[Datenhexe]

Hallo smalbop,

ja, ich verstehe die Fragerei ja. Und ich weiß, dass meine Erklärungen zu wünschen übrig lassen. So, nun zum nächsten Versuch der Beschreibung:

Das Warenbewegungsprotokoll hat immer feste Werte. Es kann vorkommen dass diese ergänzt werden, und dann auch evt. mehrere Zeilen eingefügt werden, aber selten. In der Tabelle Verpackung dagegen ändern sich die Werte immer, je nach Abverkauf der Artikel. Dies geschieht aber nicht händisch, sondern es werden immer wieder alle Daten aktualisiert indem ich diese aus einer HTM Datei einlese. Der Aufbau der Tabelle ändert sich nicht, nur immer wieder die Artikelnummern und die Werte dahinter. Da die Abverkäufe ja immer variieren.

Und nein, es sollten eigentlich immer alle Artikelnumemrn im Warenbewegungsprotokoll vorhanden sein.

Es müsste also so laufen, dass wenn die Tabelle Verpackung upgedatet ist, sich automatisch der geringste Lagerbestand pro Fertigartikel in Spalte K einliest.

LG Martina

[smalbop]

Hallo Martina

Würde was dagegen sprechen, die frisch importierten Daten 1. aufsteigend nach Artikelnummern und 2. absteigend nach Lagerbestand zu sortieren, bevor man ein Makro drüberlaufen lässt (das dann allerdings zweckmäßigerweise händisch ausgelöst werden müsste)?

Können/müssen alle vorkommenden importierten Artikelnummern in der Protokolltabelle mit einem neuem Mindestbestand versehen werden?

Gruß
smalbop

[Datenhexe]

Hallo smalbop,

nein dagegen würde nichts sprechen. Die Artikelnummern der Fertigartikel sind ja vom System her schon aufsteigend sortiert, die andere Sortierung kann ich in das schon bestehende Makro einbauen. Das neue Makro sollte eigentlich auch an das bestehende angehängt werden, damit alles auf einmal durchläuft. Einen Mindestbestand habe ich nur für die Fertigartikel und nicht für die Verpackungsmittel. Der ist für diese Auswertung aber auch garnicht relevant. Wäre der wichtig??

LG Martina

[Thomas_Ramel]

Grüezi Martina

Hast Du meinen Beitrag weiter oben mit der eingebauten Pivot-Tabelle schon mal näher angesehen?

/t/min-wert-ausgeben-in-abhaengigkeit-von-einem-zell…

Damit würden alle Makro-Geschichten entfallen.

–
Mit freundlichen Grüssen

Thomas Ramel

• MVP für MS-Excel -

[Datenhexe]

Hallo Thomas,

mit Pivottabellen kann ich umgehen. Aber leider deckt diese Problemlösung bei weitem nicht mein Problem. Ich habe eine feste Tabelle, in die die Daten immer wieder neu eintragen werden müssen. Das Layout ist vom Kunden vorgegeben und muss eingehalten werden. Ansonsten hätte ich es auf diesem Wege schon lösen können.

Aber vielen Dank für Deine Hilfe.

LG Martina

[Thomas_Ramel]

Grüezi Martina

mit Pivottabellen kann ich umgehen. Aber leider deckt diese
Problemlösung bei weitem nicht mein Problem. Ich habe eine
feste Tabelle, in die die Daten immer wieder neu eintragen
werden müssen. Das Layout ist vom Kunden vorgegeben und muss
eingehalten werden.

Dann erstelle die PT als Zwischenschritt und hole mit der Funktion PIVOTDATENZUORDNEN() die Daten in das Layout des Kunden.

Das scheint mir immer noch einfacher zu sein, als eine Makro-Lösung (ein Makro kannst Du dann ja immer noch verwenden um die PT regelmässig zu aktualisieren).

–
Mit freundlichen Grüssen

Thomas Ramel

• MVP für MS-Excel -

[Datenhexe]

Hallo Thomas,

manchmal sieht man den Wald vor lauter Bäumen nicht. Vielen vielen Dank. Mit Deinem Vorschlag klappt die ganze Sache 1A. Viel viel Zeit und Arbeit gespart. Im Moment lese ich die Daten noch über Summewenn ein, da ich die Funktion PIVOTDATENZUORDNEN()noch nicht kenne, aber ich werde mich in den nächsten Tagen schlau machen und damit arbeiten.

Noch mal vielen Dank für Eure grenzenlose Geduld mit mir und die gute Hilfe.

LG Martina

[Datenhexe]

Hallo an alle,

noch mal zum Abschluss. Ich habe jetzt auch mein letztes Problem gelöst und kann mit PIVOTDATENZUORDNEN()arbeiten.

LG Martina