Office: (Office 2003) Dynamisches Suchfeld inkl. "Kopierfunktion"

Ich wünsche einen angenehmen Tag in die Woche !

Ich habe eine Excel Aufgabe vor, die sich eigentlich einfach anhört, aber unlösbar für mich ist.

Ich habe eine Excel Datei mit 2 Arbeitsblättern Tabelle1 und Tabelle2.

Ich möchte gerne, dass sich die Zelle A1 in Tabelle 1 wie ein Suchfeld verhält, dass in Tabelle2 im Bereich b3:d2000 sucht.

Gesucht werden soll wie folgt: Man gibt einen oder mehrere Strings getrennt durch Leerzeichen in das Feld A1 in Tabelle1 ein.

Für einen Suchstring: Wenn der eingegebene String irgendwo im Bereich b3:d2000 gefunden wird, soll die jeweilige Zeile, beginnend ab Spalte B bis Spalte I in die Tabelle1 beginnend ab A10 kopiert werden.

Wenn der Suchbegriff(String) in mehreren Zeilen gefunden wird, sollen auch alle jeweiligen Zeilen kopiert werden.

Für mehrere Suchstrings: Gesucht wird nach Zeilen des o.g. Suchbereichs, welche alle eingegebenen Strings aus A1 enthalten, dabei soll die Reihenfolge der vorkommenden Strings unerheblich sein. Dann soll wieder genauso wie oben kopiert werden.

Die Formatierung der zu kopierenden Zellen soll außerdem beibehalten werden, also Breite der Zellen etc.

Ich hoffe Ihr könnt mir folgen.

Ich wäre sehr dankbar für Hilfe hierbei !

 

Hallo Simon,

im Codemodul des Arbeitsblattes "Tabelle1" habe ich dir ein VBA-Makro eingebaut, das deine gewünschte Funktionalität reproduziert.

In Zelle A1 werden die Kriteriumswörter (getrennt durch Leerzeichen) eingegeben. Sobald du die aktive Zelle nach Zelle A2 (gelbe Zelle) verschiebst, wird der Suchvorgang im Arbeitsblatt "Tabelle2" ausgeführt und die gefundenen Zeilen ab Zelle A10 des Arbeitsblattes "Tabelle1" ausgegeben.

 

Guten Morgen,

vielen Dank, funktioniert schon fast perfekt.

Ich habe mich wahrscheinlich vorher etwas unglücklich ausgedrückt, ich möchte nicht nur nach ganzen Wörtern suchen, sondern auch nach Teilstrings.

Wenn ich also z.B. nach "au" suche, sollen alle Zeilen ausgegeben werden die diesen Teilstring enthalten, das heißt auch, dass Groß-/Kleinschreibung nicht beachtet werden soll.

Ich meinte mit "Formatierung der zu kopierenden Zellen beibehalten", dass die Spaltenbreiten aus Tabelle2 beibehalten werden sollen, aktuell haben die in Tabelle1 kopierten Zellen, soweit ich sehen konnte, die AutoFit-Funktion.

Danke !

 

Hallo,

in der beiliegenden Excel-Datei habe ich jetzt die gewünschten Änderungen eingebaut:
1) Beliebige Teilstrings werden erkannt (anstelle ganzer Wörter)
2) Groß/Kleinschreibung wird ignoriert beim Suchen
3) Spaltenbreiten des Quellbereichs werden in den Ausgabebereich übernommen.

 

Hallo,

der Code ist einfach genial.

Danke nochmals !

 

Ich hätte jetzt doch noch eine Frage, was müsste ich im Code ändern, um die Position des Suchfeldes zu verändern ?

Also wenn das Suchfeld z.B. in der Zelle M20 sein soll.

Danke !

 

Hallo,

ich habe dir in die Arbeitsmappe 2 Namen für benannte Zellen eingefügt:
Name "Suchfeld": diese Zelle befindet sich im Arbeitsblatt "Tabelle1" in Zelle M20
Name "Ausgabezeilen": diese Zelle befindet sich im Arbeitsblatt "Tabelle1" in Zelle A10 (linke obere Ecke des Ausgabezeilenbereiches)
und habe das restliche Makro etwas angepasst.

Dadurch befindet sich das Suchfeld jetzt in der Zelle M20 des Arbeitsblattes "Tabelle1". Wird die aktive Zelle in die darunterliegende Zelle (=Zelle M21) verschoben, so wird der Such- und Kopiervorgang ausgelöst mit den Suchwerten aus Zelle M20, wobei die gefundenen/kopierten Zeilen ab Zelle A10 zu finden sind.
Die aktive Zelle steht zum Abschluss des ganzen Vorgangs wieder im Suchfeld (Zelle M20).

Du brauchst also die Adressen für die beiden benannten Zellen im Namensmanager abzuändern. Du musst nur darauf achten, dass das "Suchfeld" und der "Ausgabezeilen"-Bereich sich nicht in die Quere kommen.

 

Guten Morgen,

danke so kann man das Such- und Ausgabefeld wirklich sehr komfortabel ändern.

Mir ist noch aufgefallen, das jetzt in Tabelle1 teilweise Zellen nach dem kopieren der gefundenen Begriffe "gelöscht" werden.

D.h. Inhalte von Zellen links, rechts sowie oberhalb, unterhalb des Ausgabebereichs in Tabelle1 verschwinden.

Es geht mir hierbei nur um Zellen die vor dem Kopiervorgang schon in Tabelle 1 standen, diese würden sich dann nur links, rechts und oberhalb des Ausgabebereichs befinden.

Die Anzahl der zu kopierenden Zellen aus Tabelle2 (Spalte B-I) also 8 wird immer gleich bleiben.

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

Vielen Dank !

 

Hallo,

folgende Codezeilen sind in meinem Makro enthalten:

Code:

  '...
  Set RgZiel = Range("Ausgabezeilen")
  RgZiel.CurrentRegion.Clear
  '...

Diese werden benötigt, um die Daten des vorherigen Ausgabezeilen-Bereiches zu entfernen, sodass sie die aktuelle Datenausgabe nicht stören.

In der ersten dieser Zeilen wird anhand des benannten Bereiches "Ausgabezeilen" die Bereichsvariable "RgZiel" festgelegt. Geht man von den Angaben in den vorigen Postings aus, müsste "RgZiel" den Zellbereich "Tabelle1!A10" enthalten.

In der zweiten dieser Zeilen steht, dass ausgehend von "RgZiel" mit der Methode "CurrentRegion" ein neuer Zellbereich ermittelt wird: und zwar ein rechteckiger Zellbereich, der solange zu vergrößern ist, bis er oberhalb, rechts, unterhalb von lauter Leerzellen begrenzt wird (links befindet sich ohnehin der Rand des Arbeitsblattes).
Auf diesen neuen Zellbereich wird die Methode "Clear" angewendet, die alles löscht, was sich im Innern des Bereiches befindet.

Hast du in Zellen, die unmittelbar an den achtspaltigen Ausgabezeilen-Bereich, der in Zeile 10 beginnt, angrenzen, irgendwelche Inhalte gespeichert, ohne dass eine Leerzeile bzw. Leerspalte dazwischen ist, werden diese notgedrungen in den Löschbereich einbezogen.

In der beiliegenden Datei habe ich diesen Code wie folgt geändert:

Code:

  Dim Zll&
  Set RgZiel = Range("Ausgabezeilen")
  SpAnz% = 8 'Spaltenanzahl des Ausgabebereiches
  With RgZiel
    Zll& = .End(xlDown).Row - .Row + 1
    If .CurrentRegion.Cells.Count <= 5 * SpAnz% Then Zll& = 5
    RgZiel.Resize(Zll&, SpAnz).Clear
  End With

Dieser Code löscht jetzt einen achtspaltigen rechteckigen Zellbereich, der in Zeile 10 und Spalte A beginnt. Die Zeilenanzahl dieses Bereiches richtet sich nach der Anzahl der mit Daten belegten Zellen in Spalte A, beginnend bei Zelle A10. Das wird durch die Codezeile
Zll& = .End(xlDown).Row - .Row + 1
ermittelt. Die Verkomplizierung ergibt sich für den Fall, dass nur 1 oder keine Ausgabezeile an vorherigen Daten vorhanden sind:
If .CurrentRegion.Cells.Count <= 5 * SpAnz% Then Zll& = 5
Die Zeile
RgZiel.Resize(Zll&, SpAnz).Clear
schließlich bewirkt den Löschvorgang.

 

Hallo,

klingt logisch...mir war vorher nicht klar, wie der zu löschende Zellbereich definiert wurde.

Ich bedanke mich abermals, jetzt passt der Code perfekt zu meinem Anwendungsfall.

Ich wünsche noch einen angenehmen Abend.