hallo freunde

ich benötige mal eure hilfe. meine excel-datei besteht aus 3 tabellenblättern. jedes tabellenblatt zeigt kunden mit einem bestimmten status an.

tabellenblatt 1 = neukunde
tabellenblatt 2 = bestandskunde
tabellenblatt 3 = ex-kunde

jeder neue kunde wird im tabellenblatt 1 mit seinen persönlichen daten in einer zeile erfasst. am ende der zeile gibt es ein drop-down-feld, aus dem der status "neukunde" ausgewählt wird. sollte der neukunde zum bestandskunden werden, müssen seine daten in das tabellenblatt 2 verschoben werden. die änderung des status erreiche ich, in dem ich im tabellenblatt 1 im entsprechenden drop-down-feld den status von "neukunde" in bestandskunde ändere. so könnten am ende 3 kunden in blatt 1 stehen, die mit dem status "bestandskunde" gekennzeichnet worden sind. ich möchte nun mittels makro erreichen, dass genau diese kunden anhand des status ermittelt, nur die datenreihe ausgeschnitten und an das ende der kundenreihen im tabellenblatt 2 kopiert werden sollen.

wie stelle ich dass den am schlausten an??

schon mal vielen dank für eure hilfe

Zunächst mal: Willkommen im Forum.

Nun zum Thema:

Es ließe sich sogar machen, dass die Zeile automatisch von einem Tabellenblatt in ein anderes verschoben wird, wenn man "bestandskunde" im Drop-Down-Feld auswählt.

Im Worksheet_Change-Ereignis der Tabelle "neukunde" zunächst abfragen, ob sich Target, also die geänderte(n) Zelle(n), in der Spalte mit dem "Kunden-Status" befindet. Wenn ja, überprüfen, ob Target nun den Wert "bestandskunde" hat. Dann die Zeile ausschneiden und in der Tabelle "bestandskunde" einfügen.

Aufpassen: Target kann auch ein Zellbereich sein!


Wenn du es nicht so haben willst, sondern erst auf "Knopfdruck" alle auf "bestandskunde" geänderten Neukunden verschoben werden sollen, könntest du mit Hilfe von Worksheetfunction.CountIf und Worksheetfunction.Match die Zeilennummer(n) ermitteln und die entsprechenden Zeilen ausschneiden und einfügen.

Denke mit Worksheet_Change arbeiten wäre nicht sinnvoll, wenn man das Makro auch über die Aktivierung des Drop-Down-Feldes starten kann (nehme mal schwer an das das ein Formular-Feld ist das eingefügt wurde). Damit erspart man sich die Überprüfung ob eine Änderung vorgenommen wurde. Wird das Drop-Down-Feld aktiviert wird das Makro automatisch ausgeführt und mit if Range("ADRESSE").Value = "1" oder halt 2 oder 3 je nachdem an welcher Stelle Bestandskunde steht kann überprüft werden was die Auswahl war und entsprechend eine Aktion ausgeführt werden.

Aber irgendwie kann ich mir den Vorgang aber nicht wirklich sinnvoll vorstellen.

Grundsätzlich ist die Aktion relativ leicht:

Sub AusschneidenEinfügen

If range("RückgabeAdressedesDropDownFeldes").Value = 2 Then ' halt was Bestandskunden entspricht bei der Rückgabe

range("a1:f1").cut ' der Range-Bereich sollte halt dann die Adressfelder umfassen

With Sheets("Tabelle2")

.Activate

.range("A65500").End(xlUp).Select ' springt auf das letzte Feld der Spalte A das Inhalt aufweisst, kannst auch B oder C angeben. Ist nur wichtig das es eine Spalte ist die IMMER einen Inhalt aufweist.

End With

ActiveCell.Offset(1, 0).Select ' musst ja noch eine Zeile runter, sonst wird in das letzte Feld mit Inhalt geschrieben und damit würdest Du überschreiben

ActiveCell.Paste

End Sub

ABER ... dafür muss jedes Drop-Down-Feld eine eigene Rückgabe-Adresse haben. wo es eben die getroffene Auswahl als Wert 1, 2 oder 3 hinschreibt. @ Roddy deswegen funzt Deine Lösung auch nicht, da die Rückgabe des Drop-Down-Feldes nicht ein String ist sondern eine Zahl für die getroffene Auswahl.

Und nochmal ABER ... was ist mit den ganzen ausgeschnitten Zeilen in der Zeit? Das sieht dann mit der Zeit wie ein löchriger Käse aus und überall stehen am Ende verwaiste Drop-Down-Felder. Vor allem wieviel Kunden willst Du verarbeiten? Bei 10 oder 20 lohnt sich wohl der Aufwand für ein Makro nicht und bei 100 oder mehr wirst Du jeck in der Birne wenn Du 100 oder mehr Drop-Down-Felder einfügen und jedem eine eigene Rückgabe Adresse zuweisen musst.

Wenn nur Du die Tabelle nutzt, dann wäre es sinniger auf die Drop-Down-Felder zu verzichten. Schreibe einfach in einer Spalte (natürlich muss dann die Schreibweiser immer exakt identisch sein) per Hand Neukunde, Bestandskunde und Ex-Kunde rein.

Dann kannst Du das Makro tatsächlich per Worksheet_Change Ereignisse immer bei Änderung des Zellinhaltes in der Spalte ablaufen lassen:

Die nachfolgende Sequenz geht davon aus, dass sich der Eintrag in Spalte 5 also "E" befindet.

Private Sub Worksheet_Change(ByVal Target As Range) ' dieser Eintrag muss in den Codebereich für Tabellenblatt selber rein

If Target.Column = 5 Then Kundenübertragen

End Sub

Sub Kundenübertragen ' dieser Eintrag in ein Modul rein

Dim I As Integer

Sheets("Tabelle1").Activate

With Sheets("Tabelle1")

I = 1

Do Until .cells(I, 5).Value = ""

If .Cells(I, 5).Value = "Bestandskunde" Then

.cells(I, 0).Select

Selection.EntireRow.Cut

With Sheets("Tabelle2")

.Activate

.range("A65500").End(xlUp).Select

ActiveCell.Offset(1, 0).Select

Selection.Paste

End With

Sheets("Tabelle1").Activate

Exit Do

End If

I = I + 1

Loop

End With

End Sub

Wie gesagt, ausgehend davon das in Spalte 5 oder eben "E" der Eintrag für die Kundenart steht. Nach jedem erfolgreichen Treffer schneidet das Makro die komplette Zeile aus und fügt sie im Tabellenblatt2 am Ende wieder ein.

Hab den Code nicht geprüft sondern nur auf die schnelle mal getippt. Hoffe das mir kein Schnitzer unterlaufen ist. Sollte er nicht funktionieren gib bitte nochmals kurz Bescheid.

Gruß

Rainer

Hallo Ihr Zwei

Erst mal vielen Dank für die beiden Postings. Ihr habt mir damit mal wieder ganz klar meine Grenzen aufgezeigt. Werde mich über Ostern damit "auseinandersetzen".

Das Drop-Down-Feld wird sich allerdins nicht umgehen lassen, da die unterschiedliche Schreibweise bisher genau das Problem war. Viele Datensätze wurde nicht berücksichtigt, da sich immer wieder Fehler eingeschlichen haben.

Ich werde Euch am Wochenende berichten wie es gelaufen ist.

Bis dahin schöne Feiertage und vielen Dank.

Gruß

Jürgen

Gern geschehen.

Aber wenn Du sagst, dass die Schreibweise das Problem ist gehen also mehrere Nutzer mit dem Teil um.

Dann wäre es aber sinnvoller, dem Nutzer die Möglichkeit zu geben den Wechsel des Kundenstatus über ein Ereignis aufzurufen und dort dann per Optionsfeld den Status zu ändern und nach Beendigung dann das Umschreiben das Datensatzes zu veranlassen.

Könnte z.B. funktionieren in dem Du die Spalte in dem der Kundenstatus steht mit einem farblichen Hintergrund versiehts. Der könnte z.B. hellgrün sein und hat den Farbindex 35.

Könnte man ein Ereignis auf Doppelklick programmieren. Immer wenn der User auf ein hellgrünes Feld einen Doppelklick macht und der Inhalt größer Leer ist bekommt er eine kleine UserForm in der er nur die 3 Auswahlmöglichkeiten als OptionButton 1 - 3 vorbelegt bekommt.

Klickt er dann einen der OptionButtons an, prüft die Routine welcher Status jetzt gültig ist und verschiebt den Kunden dann in das passende Tabellenblatt rein.

Ist im Prinzip relativ simple zu machen (10 bis 15 Minuten Arbeit ^^) und hat den riesen Vorteil Du musst nicht pro Kunde ein Drop-Down-Feld anlegen, sondern machst es einmal und es passt immer ob Du nun 100 oder 10.000 Kunden eingetragen hast. Desweiteren kann man dann auch die Leerzeilen rausnehmen und nach jeder Änderung die Datensätze schön fein säuberlich nach Nachnamen oder was auch immer wieder sortieren.

Da die Prozedur dann über die Farbe hellgrün (wobei die Farbwahl natürlich frei ist) und das Ereignis Doppelklick aufgerufen wird, wird sie genauso problemlos für die Tabellen Bestandskunden und Ex-Kunden funktionieren(oder kann es nicht passieren, dass ein Bestandskunde ein Ex-Kunde wird oder ein Ex-Kunde wieder zum Bestandskunden wird? ).

Gruß

Rainer

@ raist10:

Ich hatte angenommen, dass Xenotronic mit "DropDown-Feld" eine Zelle gemeint hat, die per Gültigkeitsregel eine DropDown-Liste erhalten hat.

genauso iast es raist. das drop-down-feld wird mit "gültigkeit" generiert. dieses kannst ich doch einfach in alle anderen zeilen kopieren. beim auslesen des werter dürfte es doch keinen unterschied machen, aus welcher quelle die referenz kommt, oder?

Hhmmm ... dann scheine ich gerade irgendwie etwas nicht zu verstehen.

Von der Einfachheit her der Problemstellung, gehe ich davon aus das mit Drop-Down-Feld die Teile gemeint sind, die man in Excel-Sheets über die Ansicht Formularfeld (heissen dort Kombinationsfeld) einbaut. Deren Rückgabewert für die Auswahl ist 1 oder 2 oder eben was auch immer, aber kein Text-String ala "Bestandskunde". Um aus der Rückgabe dann einen Textstring zu basteln gibt man eine Zeile ala "Wenn(A1=1;"Neukunde";wenn(A1=2;"Bestandskunde"; wenn(A1=3;"Ex-Kunde";""))) oder so in der Richtung ein.

Die Rückgabe eines Textstrings könnte man aber über den Einbau eines OLEObjects ListBox oder ComboBox erreichen.

Bei beiden Möglichkeiten habe ich das Thema die Positionierung des Feldes vorzugeben ... irgendwie fehlt mir da gerade die Möglichkeit die Position so eines Objektes passend zur Cursor-Position in einer Zelle zu generieren, zumindest nicht ohne gröberen Aufwand. Letztendlich wird ja die Position eines Feldes durch eine Angabe zu Top und Left bestimmt. Diese Positionselemente nun variabel aufzubauen um auch wirklich hinter jeder gültigen Adresse ein Drop-Down-Feld zu bekommen ... öhem jaaaa, viel Spaß ... okay zugegeben, habe mich damit auch nie wirklich beschäftigt also gut möglich das ich da was völlig übersehe, bzw. mir eine Methode sowas schnell und einfach zu machen unbekannt ist.

Aber egal wie ... einfachere, schnellere Methode dürfte trotzdem sein, abhängig von der angewählten Zelle per Ereignis ein einziges UserForm aufzurufen und dort die Eingabe vorzunehmen, die gemachte Eingabe auszulesen und dann in die entsprechende Zelle einzufügen und im Anschluß die Prozedur aufzurufen die dann in Abhängigkeit des Eintrages die Aktion auslöst.

Oder sehe ich da was wirklich so völlig falsch?

Gruß

Rainer

Okay vergesset es ... hat bei geschnackelt. Die Variante über Daten/Gültigkeitsregel im Excelmenü zu gehen habe ich völlig vergessen.

Aber gut, richtig dann habe ich den String dort stehen. Ändert aber nichts am ursprünglichen Makro, dem ist es relativ wurscht ob da nun 1 oder Bestandskunde als Vergleich steht, bzw. woher der Ursprungswert kommt, das ist richtig.

Gruß

Rainer

roddy schrieb:

Zunächst mal: Willkommen im Forum.

Nun zum Thema:

Es ließe sich sogar machen, dass die Zeile automatisch von einem Tabellenblatt in ein anderes verschoben wird, wenn man "bestandskunde" im Drop-Down-Feld auswählt.

Im Worksheet_Change-Ereignis der Tabelle "neukunde" zunächst abfragen, ob sich Target, also die geänderte(n) Zelle(n), in der Spalte mit dem "Kunden-Status" befindet. Wenn ja, überprüfen, ob Target nun den Wert "bestandskunde" hat. Dann die Zeile ausschneiden und in der Tabelle "bestandskunde" einfügen.

Aufpassen: Target kann auch ein Zellbereich sein!


Wenn du es nicht so haben willst, sondern erst auf "Knopfdruck" alle auf "bestandskunde" geänderten Neukunden verschoben werden sollen, könntest du mit Hilfe von Worksheetfunction.CountIf und Worksheetfunction.Match die Zeilennummer(n) ermitteln und die entsprechenden Zeilen ausschneiden und einfügen.

Also diese Lösung scheint genau dem zu entsprechen was ich suche. Die andere Lösung hat auch leider nicht funktioniert. nehmen wir also an in Zeile 2 stehen Kundendaten, und in Spalte E wird der Status hinterlegt. Mein Ziel ist, durch verändern des Status (mittels Drop-Down-Feld/Gültigkeit) die Zelle automatisch auszuschneiden, und an das Ende der Liste im 2 Tabellenblatt wieder einzufügen.

Vielen Dank für Eure tolle Unterstützung.

Ich hab folgendes bei mir ausprobiert und es hat funktioniert. Die Tabellennamen müssen dabei den möglichen Einträgen entsprechen. Es muss also eine Tabelle "Neukunde", eine Tabelle "Bestandskunde" und eine Tabelle "Ex-Kunde" geben.

In einem beliebigen Modul diese Prozedur reinstellen:



Und bei den drei Tabellen jeweils das reinschreiben:



Wenn man sich nun beispielsweise in der Tabelle "Neukunde" befindet und den Text in Spalte E auf "Bestandskunde" ändert, wird automatisch die komplette Zeile an das Ende der Tabelle "Bestandskunde" verschoben.

werde es gleich mal ausprobieren, danke!!!

hat nicht funktioniert. habe sicherlich einen kleinen bug drin.

ich habe eine neue mappe geöffnet. anschließend die tabellenbätter umbenannt. anschließend in spalte 5 (über gültigkeit) die 3 auszuwählenden status hinterlegt. dann im editor der mappe ein modul hinzugefügt und die entsprechende prozedur eingefügt. anschließend den 2. quelltext in jedes einzelne tabellenblatt eingefügt und denn editor geschlossen.

sobald ich den status in spalte 5 ändere wird mir immer ein laufzeitfehler angezeigt.

ist bestimmt nur eine kleinigkeit.

danke vorab.

Um helfen zu können, müsste man nun wissen, in welcher Code-Zeile der Fehler auftritt und was genau für eine Fehlermeldung kommt.

EDIT: Ein Schuss ins Blaue: Prüf mal nach, ob die Tabellennamen und die Einträge in der Gültigkeitsliste richtig geschrieben sind und sich kein Tippfehler eingeschlichen hat. Hier noch eine kleine Abänderung der Prozedor um diesen Fehler zu isolieren:

Super, ich bin einen riesenschritt weiter. nun habe ich beiden texte in meine bestehende mappe implementiert. er schneidet die die zeile beim statuswechsel zwar aus, zeigt aber gleichzeit den fehler an, dass die tabelle (in meinem Fall) 107774 nicht vorhanden sei. die ausgeschnittene zeile fügt er zwar im richtigen blatt wieder ein, allerdins tut er das in zeile 500.

wir sind ganz dicht dran, ich spür das.

Nochmals vielen Dank für Ihre Geduld. Sie sind mir wirklich eine große Hilfe.

Das rührt wahrscheinlich daher, dass es eine Zahl ist. "Zelle.Value" wird dann als Zahl aufgefasst, während der Name eines Tabellenblatts as String, also als Text, gespeichert ist. Und da der Vergleich "107774" = 107774 falsch ergibt, wird die Tabelle nicht gefunden. Wenn du in den Zeilen 6 und 9 das Zelle.Value jeweils durch CStr(Zelle.Value) ersetzt, müsste es funktionieren. Damit wird nämlich der Wert der Zelle in einen String konvertiert.

Das mit der Zeile 500 könnte etwas mit deinem Tabellenaufbau zu tun haben, wenn du runter bis Zeile 499 z. B. Formeln oder andere Inhalte oder Formatierungen hast.

Probiers mal, die Zeile 21 hierdurch zu ersetzen:



Wie raist10 schon erwähnt hat, muss es jedoch eine Spalte sein, die bei jedem Kunden ausgefüllt ist:

raist10 schrieb:

.range("A65500").End(xlUp).Select ' springt auf das letzte Feld der Spalte A das Inhalt aufweisst, kannst auch B oder C angeben. Ist nur wichtig das es eine Spalte ist die IMMER einen Inhalt aufweist.

P.S.: Kannst mich/uns ruhig mit du/ihr anreden. In Internetforen ist das normalerweise so üblich.

Hallo Roddy

Das hat ja schon mal hingehauen. Allerdings stehe ich nun vor einem Problem der anderen Art. Den Statuswechsel habe ich ja über ein Listenfeld (Gültigkeit) vollzogen. Die Quelle war bislang in der gleichen Tabelle. Nun habe ich ein weiteres Tabellenblatt hinzugefügt, um dort die Daten der Listenfelder zu hinterlegen. Habe den Quellbereichen Namen gegeben, um diese tabellenübergreifend einsetzten zu können. Wenn ich nun aber den Statuswechsel (ohne verschieben Funktion) durchführe, kommt immer folgende Fehlermeldung "Die Tabelle "beantragt" ist nicht vorhanden". Habe schon häufig Gültigkeiten über mehrere Tabellenbätter angewendet, aber das ist neu.