Profi-Excel.de

Die Seite für den professionellen Umgang mit Excel und VBA

«
»

Mehrfach Suchen und Ersetzen

21. September 2006

In den beiden Arrays können die korresponierenden Begriffe eingetragen werden. Momentan werden die Buchstaben a durch 1, b durch 2, usw. ersetzt. Die Inhalte in den Arrays müssen natürlich den Bedürfnissen angepasst werden. Durch die For-Schleife werden die jeweiligen Einträge des Arrays der Replace-Methode des Range-Objekts übergeben. Das Range-Objekt stellt hier den benutzten Bereich des aktiven Tabellenblatts dar.

PLAIN TEXT
VBA:
  1. Sub mehrfachSuchenUndErsetzen()
  2. 'sucht im aktiven Tabellenblatt jeweils die Eintraege aus
  3. 'suchArray und ersetzt mit ersetzArray
  4. '09-2006
  5. 'E.Bimczok http://profi-excel.de
  6. Dim suchArray()
  7. Dim ersetzArray()
  8. Dim k As Long
  9.  
  10. suchArray = Array("a", "b", "c")
  11. ersetzArray = Array("1", "2", "3")
  12.  
  13. For k = LBound(suchArray) To UBound(suchArray)
  14.   Call ActiveSheet.UsedRange.Replace(suchArray(k), _
  15.                                      ersetzArray(k), _
  16.                                      , _
  17.                                      , _
  18.                                      False)
  19. Next k
  20.  
  21. End Sub

Erstellt am Donnerstag 21. September 2006 um 10:21 pm und abgelegt unter VBA Makros. Themen: none
Kommentare zu diesen Eintrag im RSS 2.0 Feed. Sie können einen Kommentar schreiben, oder Trackback auf ihrer Seite einrichten.

19 Kommentare zu “Mehrfach Suchen und Ersetzen”

  1. Kalisch sagt:
    Montag 23. Oktober 2006 um 5:19 pm

    Ich hätte gerne eine Suchfunktion für excel mit der ich verschiedene und vor allem mehrere begriffe gleichzeitig in einer Tabelle suchen kann.
    z.B.: Dell +Laptop

    gibt es da eine Möglichkeit oder sogar ein fertiges Plug-In????

    Bin ziemlich verzweifelt und ein Newbie noch dazu!
    HELP!!!

  2. Eike sagt:
    Donnerstag 26. Oktober 2006 um 7:53 pm

    Hallo Kalisch,

    wenn die beiden Ausdrücke in einer Zelle stehen, dann kannst du die Suche über die normale Suchfunktion (Strg+f) ausführen durch Eingabe von Dell*Laptop.
    Einschränkung: Die Worte müssen in exakt dieser Reihenfolge vorkommen.

    Ansonsten hast du alle Möglichkeiten über VBA.

    Gruß
    Eike

  3. Dickie sagt:
    Montag 19. Februar 2007 um 7:48 pm

    Wie würde es aussehen, wenn nur eine Spalte im aktiven Tabellenblatt durchsucht werden darf ?

  4. Eike sagt:
    Montag 19. Februar 2007 um 8:56 pm

    Hallo Dickie,

    du müsstest anstelle von “ActiveSheet.UsedRange.Replace” nur “ActiveSheet.Columns(1).Replace” einsetzen.

    Die Zahl, hier “1″, gibt die Spaltennummer an.

    Gruß
    Eike

  5. Firebird sagt:
    Dienstag 24. Februar 2009 um 2:02 pm

    Hallo.

    Gibt es eine Möglichkeit in Excel unter der Funktion “Suchen” in einer Spalte alle Werte zu suchen die Über Wert 0 liegen?

    Nach Minuszahlen kann ich durch eingabe von ~- suchen, aber ich muss alle die Suchen können, die Wert über Null haben.

    Oder aber ich suche alle mit Wert – und müsste dann irgendwie sagen können: Ganze Zeile markieren und kopieren?

    Geht das irgendwie

    Mfg
    Firebird

  6. Eike sagt:
    Mittwoch 25. Februar 2009 um 12:07 am

    Hallo Firebird,

    über den Suchen-und-Ersetzen-Dialog bzw. die oben verwendete Replace-Methode des Range-Objekts ist mir keine Lösung bekannt.

    Dennoch zwei Ansätze, Werte größer Null zu suchen:
    -Eine For-Each Schleife in VBA programmieren und alle Werte mit Cell.Value>0 prüfen.
    -Den Autofilter (Daten-Filter-Autofilter) verwenden und benutzerdefiniert filtern nach größer Null.

    Gruß
    Eike

  7. melvin sagt:
    Dienstag 17. März 2009 um 3:40 pm

    Hallo,

    wie schaffe ich es denn mit diesem Makro 2 Zellen in unterschidlichen Spalten zu bearbeiten?

    Ich möchte den Inhalt aus Zelle1 und Spalte1 gegen einen immer gleichen Teil aus Zelle2 und Spalte2 austauschen.

    BSP:
    Z1,S1: mallorca Z1,S2: reise nach xxx
    Z2,S1: teneriffa Z2,S2: reise nach xxx
    usw.

    Für xxx soll dann “mallorca”, danach “teneriffa” und immer so weiter stehen.

    Wäre für Eure Hilfe sehr dankbar.

  8. Eike sagt:
    Dienstag 17. März 2009 um 8:47 pm

    Hallo Melvin,

    in diesem Fall würde ich ein Formel empfehlen:

    =WECHSELN(B1;”xxx”;A1)

    Gruß
    Eike

  9. Peter sagt:
    Donnerstag 20. Mai 2010 um 8:43 am

    Hi

    das Sucu und Find MAkro ist gut.
    wie kann man es erweitern, daß bei Nichtübereinstimmung der letze gültige Wert erhalten bleibt?
    In diesem Beispiel wird nach verschiedenen “R* Gesucht.Ersetzt werden diese aber durch gleiche MRF*

    Hier ein Beispiel
    suchArray = Array(“R021F”, “R022F”, “R023F”, “R024F”, “R025F”, “R026F”, “R031F”, “R032F”, “R033F”, “R034F”, “R035F”, “R036F”, “R041F”, “R042F”, “R043F”, “R044F”, “R045F”, “R056F”, “R061F”, “R062F”, “R063F”, “R044F”, “R065F”, “R066F”, “R071F”, “R072F”, “R073F”, “R074F”, “R075F”, “R076F”)
    ersetzArray = Array(“MRF1F”, “MRF2F”, “MRF3F”, “MRF4F”, “MRF5F”, “MRF6F”, “MRF1F”, “MRF2F”, “MRF3F”, “MRF4F”, “MRF5F”, “MRF6F”, “MRF1F”, “MRF2F”, “MRF3F”, “MRF4F”, “MRF5F”, “MRF6F”, “MRF1F”, “MRF2F”, “MRF3F”, “MRF4F”, “MRF5F”, “MRF6F”, “MRF1F”, “MRF2F”, “MRF3F”, “MRF4F”, “MRF5F”, “MRF6F”)

  10. Eike sagt:
    Freitag 21. Mai 2010 um 9:25 pm

    Hallo Peter,

    in diesem Fall geht das einfach mit dem Platzhalter “?” für ein Zeichen:
    suchArray = Array(“R0?”)
    ersetzArray = Array(“MRF”)

  11. Blockout sagt:
    Donnerstag 3. Juni 2010 um 10:35 am

    Hallo,

    ich habe in einer Exceldatei mehrere Tabellenblätter mit englischen Begriffen, die ich ins deutsche übersetzen will. Hierfür habe ich ein Tabellenblatt “Translation” hinzugefügt, in dem in Spalte A jeweils das englische und in Spalte B das passende deutsche Wort stehen.

    Wie müßte das VBA-Makro dann aussehen? Da es über 100 S&E-Paare sind, möchte ich diese nicht manuell in das Array reinschreiben, das soll das Makro selbständig dem Blatt “Translation” entnehmen. Und es soll nicht nur in einem Tabellenblatt sondern in der gesamten Mappe gesucht und ersetzt werden.

    Gruß Blockout

  12. Menk sagt:
    Donnerstag 16. September 2010 um 7:08 pm

    Hallo,

    das Makro funktioniert wunderbar. Vielen Dank.
    Gibt es die Möglichkeit die Applikation “Gesamten Zellinhalt vergleichen”
    in das Makro einzufügen?
    Habe das Problem z.B. wenn ich nach “1″ bei folgenden Zahlen 1,11,21,31…suche.

    Gruß

  13. Eike sagt:
    Donnerstag 16. September 2010 um 8:09 pm

    Hallo Menk,

    schau mal in die Hilfe von VBA: Das dritte Argument hilft dir weiter.
    LookAt Optional Variant. Kann eine der folgenden XlLookAt-Konstanten sein: xlWhole or xlPart.

    Der Code muss dann entsprechend geändert werden:

    Call ActiveSheet.UsedRange.Replace(suchArray(k), _
    ersetzArray(k), _
    xlWhole, _
    , _
    False)

  14. Tim sagt:
    Dienstag 18. Januar 2011 um 1:24 pm

    Hallo,

    super. Alles Funktioniert. Wie kann ich die Suche auf zwei Spalten beschränken?

    Danke & Gruß

  15. Eike sagt:
    Dienstag 18. Januar 2011 um 8:44 pm

    Hallo Tim,

    Du musst nur den Suchbereich anpassen: Anstelle von UserRange verwendest Du z.B. die Spalten B und C

    ‘Spalten B und C
    Call ActiveSheet.Range(“B:C”).Replace(suchArray(k), _
    ersetzArray(k), _
    , _
    , _
    False)

  16. Bernd sagt:
    Montag 7. März 2011 um 10:35 am

    Die Ersetzungen funktionieren super. Ich möchte gerne auch ausgegeben haben, wieviele Ersetzungen durchgeführt wurden. Ich nutze die Funktion zur Analyse der Datenqualität vor einem Import in eine Datenbank. Es ist daher interessant zu wissen, in welchen Quelltabellen die meisten Ersetzungen/Bereinigungen nötig waren. Wie komme ich an die Ausgabe, die beim manuellen Suchen/erstezen am Ende angezeigt wird?

  17. Profi-Excel.de » Excel und VBA » Mehrfach Suchen und Ersetzen mit Statistik sagt:
    Montag 7. März 2011 um 9:57 pm

    [...] "Suchen und Ersetzen" hat bereits in diesem Artikel viel Interesse erzeugt. Die Frage nach statistischen Informationen ließ sich aber nicht so einfach [...]

  18. Eike sagt:
    Montag 7. März 2011 um 10:00 pm

    Hallo Bernd,

    ich habe der Antwort einen eigenen Artikel gewidmet, sonst wäre der Code hier im Kommentar kaum leserlich. Der neue Artikel ist hier zu finden: http://www.profi-excel.de/vba_makros/mehrfach-suchen-und-ersetzen-mit-statistik.htm

  19. Basti sagt:
    Sonntag 24. Juli 2011 um 6:10 pm

    Hallo,

    Suchen&Ersetzen funktioniert sehr gut. Danke für diese Hilfe. Ich habe jetzt versucht eine größere Anzahl zu Suchen und zu Ersetzen. Alles was ich eingegeben habe (zum Suchen&Ersetzen) wird vorher in rot angezeigt/geschrieben. Leider bekomme ich dann folgenden Fehler: “Fehler beim Kompilieren: Syntaxfehler.”

    Wenn ich auf Debuggen gehe zeigt er mir “Sub mehrfachSuchenUndErsetzen()” gelb hinterlegt an. Was muss ich ändern, damit diese größere Anzahl bearbeitet werden kann?

    Danke Basti

Kommentar schreiben

XHTML: Sie können diese Tags benutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

«
»