Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1016to1020
1016to1020
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Suchen und Ersetzen schnelleres Makro

Forumthread: Suchen und Ersetzen schnelleres Makro

Suchen und Ersetzen schnelleres Makro
20.10.2008 12:19:00
Susi
Hallo liebe Excel-Freunde,
ich habe folgendes kleines Makro.

Sub Suchen_Ersetzen()
out = "15200" 'zu ersetzender String
in = "43900" 'einzufügender String
For Each cell In Selection
If cell.HasFormula = True Then
cell.Formula = Application.WorksheetFunction.Substitute(cell.Formula, out, in)
End If
Next
End Sub


Das Makro sucht in einer Formel nach dem entsprechenden Ausdruck und ersetzt diesen durch den neuen.
Es funktioniert auch, ist aber leider ziemlich langsam (über 10 min), da es doch einige Formeln sind, die so wieder bereinigt werden müssen. Sollten noch mehr Formeln hinzukommen, würde es zu einer noch größeren Verzögerung kommen.
Gibt es irgendeinen Zusatz oder eine andere Möglichkeit mein Makro schneller laufen zu lassen.
Vielen Dank im voraus für Eure Hilfe.
Bye Susi

Anzeige

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 12:34:00
Erich
Hallo Susi,
ist wichtig, dass die Ersetzung nur in Zellen vorgenommen wird, die eine Formel beinhalten?
Wenn nicht, geht das hier sicher viel schneller, ersetzt aber auch Konstanten: 

Option Explicit      ' immer zu empfehlen!
Sub Suchen_Ersetzen2()
Dim strIn As String, strOut As String
strOut = "15200" 'zu ersetzender String
strIn = "43900" 'einzufügender String
Selection.Replace What:=strOut, Replacement:=strIn, LookAt:=xlPart, _
SearchFormat:=False, ReplaceFormat:=False
End Sub

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 14:04:54
Susi
Hallo Erich,
dein Makro bleibt leider immer wieder bei der Zeile
Selection.Replace What:=strOut, Replacement:=strIn, LookAt:=xlPart, _
SearchFormat:=False, ReplaceFormat:=False
hängen und ich weiß auch nach einigem rumprobieren nicht, was ich ändern müsste.
Eine kleine Erklärung zu meinem Makro:
In meiner Tabelle steht die Formel: =TEIL('[Mappe1.xls]15200'!B2;1;40) und in dieser soll nun das Tabellenblatt "15200" durch "43900" ersetzt werden. Also hab ich durch mein Makro in der Formel eben den Wert 15200 durch 43900 ersetzen lassen.
Vielleicht erklärt das ja den Laufzeitfehler.
Bye Susi
Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 12:40:00
Rudi
Hallo,

Sub Suchen_Ersetzen()
Dim strIn, strOut, i As Long, j As Long
Dim arrTmp
strOut = "15200" 'zu ersetzender String
strIn = "43900" 'einzufügender String
arrTmp = Selection.SpecialCells(xlCellTypeFormulas).Formula
For i = 1 To UBound(arrTmp)
For j = 1 To UBound(arrTmp, 2)
arrTmp(i, j) = Application.WorksheetFunction.Substitute(arrTmp(i, j), strOut, strIn)
Next j
Next i
Selection.SpecialCells(xlCellTypeFormulas).Formula = arrTmp
End Sub


Gruß
Rudi

Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 14:15:48
Susi
Hallo Rudi,
das Makro bleibt leider bei der Zeile
Selection.SpecialCells(xlCellTypeFormulas).Formula = arrTmp
hängen. Warum kann ich leider nicht feststellen.
Gruß Susi
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 14:19:51
Rudi
Hallo,
keine Formeln im markierten Bereich?
Gruß
Rudi
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 14:31:00
Susi
Hallo Rudi,
kann eigentlich nicht sein. Hab dieses Problem mal in einer Testdatei ausprobiert. In meinen Zellen steht definitiv die Formel =TEIL('D:\Daten\[Mappe1.xls]Tabelle3'!B1;1;3) drin und in dieser soll nun "Tabelle3" mit "Tabelle2" ausgetauscht werden.
Dein Mako auf meine Testdatei angepasst lautet dann 

Sub Suchen_Ersetzen()
Dim strIn, strOut, i As Long, j As Long
Dim arrTmp
strOut = "Tabelle3" 'zu ersetzender String
strIn = "Tabelle2" 'einzufügender String
arrTmp = Selection.SpecialCells(xlCellTypeFormulas).Formula
For i = 1 To UBound(arrTmp)
For j = 1 To UBound(arrTmp, 2)
arrTmp(i, j) = Application.WorksheetFunction.Substitute(arrTmp(i, j), strOut, strIn)
Next j
Next i
Selection.SpecialCells(xlCellTypeFormulas).Formula = arrTmp
End Sub


Dennoch kommt der Laufzeitfehler wieder hoch. Bin schon langsam am verzweifeln.
Gruß Susi

Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 15:19:00
Rudi
Hallo,
das klappt bei mir:

Sub Suchen_Ersetzen()
Dim strIn, strOut, i As Long, j As Long
Dim arrTmp
strOut = "Tabelle3" 'zu ersetzender String
strIn = "Tabelle2" 'einzufügender String
arrTmp = Selection.SpecialCells(xlCellTypeFormulas).FormulaLocal
For i = 1 To UBound(arrTmp)
For j = 1 To UBound(arrTmp, 2)
arrTmp(i, j) = Replace(arrTmp(i, j), strOut, strIn)
Next j
Next i
Selection.SpecialCells(xlCellTypeFormulas).FormulaLocal = arrTmp
End Sub


Gruß
Rudi

Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 13:01:16
Tino
Hallo,
hier noch eine Möglichkeit, getestet mit 45000 Zellen.
Modul Modul1 
Option Explicit 
Sub Test() 
Dim strAlt As String 
Dim strNeu As String 
Dim iCalc As Integer 
     
    strAlt = "15200" 
    strNeu = "43900" 
 
With Application 
    iCalc = Application.Calculation 
    .Calculation = xlCalculationManual 
    .ScreenUpdating = False 
           Cells.SpecialCells(xlCellTypeFormulas).Replace strAlt, strNeu, xlPart, _
               xlByRows, False, False, False 
    .Calculate 
    .Calculation = iCalc 
    .ScreenUpdating = True 
End With 
End Sub


Gruß Tino

Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 14:23:00
Susi
Hallo Tino,
ich weiß nicht, ob ich auf dem falschen Fuß bin, aber das Makro bleibt bei der Zeile
Cells.SpecialCells(xlCellTypeFormulas).Replace strAlt, strNeu, xlPart, xlByRows, False, False, False
bei .Replace hängen mit der Fehlermeldung "Falsche Anzahl an Argumenten oder ungültige Zuweisung zu einer Eigenschaft".
Ich wollte in meiner Tabelle in den Formeln: =TEIL('[Mappe1.xls]15200'!B2;1;40)... den String 15200 (also Tabellenblatt 15200) durch 43900 ersetzen.
Gruß Susi
Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 14:49:00
Tino
Hallo,
habe Deine Formel getestet, ohne Probleme.
Nur jetzt weis ich, warum es bei dir auch so lang dauert.
Externe Zellbezüge brauchen sehr lang bei der Berechnung, wenn diese Datei nicht geöffnet ist.
Also wirst Du sehr wahrscheinlich kein schnelles Makro bekommen, nur eins dass die Datei vorher öffnet und danach wieder schließt, könnte Abhilfe schaffen.
Gruß Tino
Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 17:49:00
Rudi
Hallo Tino,

nur eins dass die Datei vorher öffnet und danach wieder schließt, könnte Abhilfe schaffen.


siehe meine Lösung mi Array.
Gruß
Rudi

AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 18:11:39
Tino
Hallo,
habe es gerade mal mit 5 verschiedenen Dateien getestet.
Die Formeln habe ich abwechselnd in die Zellen geschrieben.
Allein die Neuberechnung hängt schwer hinterher, danach habe ich diese fünf Dateien geöffnet und die Berechnung ging so schnell wie ohne externe Bezüge.
Bei beiden Versionen meine und Deine, konnte ich keinen großen unterschied feststellen.
Gruß Tino
Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 21:54:00
Rudi
Hallo,

Bei beiden Versionen meine und Deine, konnte ich keinen großen unterschied feststellen.


möchte ich sehr bezweifeln. 200-300 externe Formeln oder so in einem Array zu ändern und in einem Rutsch zurück zu schreiben, geht ratzfatz. und ohne zwischenzeitliche Neuberechnung.
Gruß
Rudi

Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 22:01:00
Tino
Hallo,
ich konnte sogar zuschauen wie Excel Zelle für Zelle
ausgelesen (neu berechet) hat, nach ca. zwei min. habe ich dies über den Taskmanager abgebrochen.
Gruß Tino
AW: Suchen und Ersetzen schnelleres Makro
21.10.2008 00:16:43
Daniel
Hi
ja klar, das Einlesen, Ändern und Zurückschreiben geht schnell, aber die Formeln müssen irgendwann auch berechnet werden und dann ist es egal, sie per Array oder Suchen+Ersetzen geändert wurden.
bei einer grösseren Anzahl von Fernbezügen hilft tatsächlich nur, die Neuberechnung erst dann durch zu führen, wenn die entsprechenden Dateien geöffnet sind.
Aus diesem Grund bin ich eigentlich davon abgekommen, Fernbezüge irgendwo in eine Tabelle zu schreiben.
wenn, dann schreibe ich ein kleines Makro, daß die entsprechende Datei öffnet und dann die Daten kopiert.
das macht meiner Erfahrung nach am wenigsten Ärger.
Gruß, Daniel
Anzeige
AW: Suchen und Ersetzen schnelleres Makro
20.10.2008 17:20:00
Erich
Hallo Susi,
nachdem du nun einige (lauffähige!) Versionen bekommen und erfolglos getestet hast,
ist klar, dass das Problem andere Ursachen hat als den oder die Codes.
Was passiert, wenn du die Ersetzung per Hand in Excel (mit Bearbeiten - Ersetzen) vornimmst?
Noch eine Frage: Ist Mappe1.xls geöffnet?
In deinem Code hast du eine Variable "in". Das kann eigentlich nicht gehen - "in" ist in VBA ein geschütztes Wort.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
;

Forumthreads zu verwandten Themen

Txt - Text suchen und ersetzen VBA
Stevie

Hie zusammen,
ich hoffe mir kann hier jemand helfen, ich habe einen Code aus dem Internet (oder sogar von der Seite hier) und den muss ich leider ein bisschen anpassen. Der Code sucht in einer Textdatei nach einer bestimmten Zeichenfolge STxtA und ersetzt diesen Text durch STxtB. Soweit so...
30.10.2008 13:09:28
Thread-ID: 1020573
3 Beiträge
Anzeige
Suchen und >Ersetzen mit VBA macht komische Sachen
SteffenS

Hallo Zusammen,
mit dem Code:
WSZ.Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ersetze ich " in allen Zellen.
Mache ich dies manuell so wird aus:
1348,52102" --> richtig 1348,...
11.01.2009 18:08:38
Thread-ID: 1038681
3 Beiträge
Suchen und ersetzen
zink

Hallo,
in einer Spalte stehen untereinander mehrere Hundert Namenseinträge nach dem Muster:
>
Name und Vorname also in einer Zelle, getrennt durch ein Komma.
Ich möchte jetzt, dass nur der 1. Buchstabe des Vornamens mit einem Abkürzungspunkt in der Zelle steht, also
><...
26.01.2009 08:32:40
Thread-ID: 1043740
6 Beiträge
Suchen und Ersetzen durch
pitt_111@gmx.de

Hallo Excel Gemeinde,
brauche wieder Eure EErfahrung und Hilfe.
In angehängter Tabelle soll nach Werten gesucht werden und diese
sollen entsprechend durch 1 ersetzt werden.
https://www.herber.de/bbs/user/59198.xls
Das suchen und ersetzen soll sich aber nur auf das betreffen...
07.02.2009 09:50:37
Thread-ID: 1047999
2 Beiträge
Wert mit VBA suchen und Zelle aktivieren
Nico

Moin
Ich habe eine Abrechnungsdatei und folgendes Problem
In einem Tabellenblatt werden Informationen wie Firmenname und Co. eingetragen.
In der letzten Spalte dieses Blatte möchte ich gerne das Datum per VBA eintragen lassen, an dem ich die Rechnungsnummer über mein Abrechnungstabe...
16.02.2009 16:07:00
Thread-ID: 1051203
2 Beiträge
Durchsuchen und in Zelle daneben ersetzen
Holger

Hallo,
irgendwie komme ich nicht weiter. Ich möchte mit einem Makro die Spalte "A1:A5000" nach der
Zeichenfolge "SV" durchsuchen und überall wo "SV" gefunden wird in der Zelle rechts daneben
(also in der gleiche Zeile in Spalte "B") das Datum "01.01.2008" eintragen.
Ein Ansat...
26.02.2009 12:25:07
Thread-ID: 1054317
4 Beiträge
Anzeige
Suchen und ersetzen ohne Neuanfang bei Zeile 1
Stefan

Hallo liebes Froum,
ich habe eine Spalte mit Werten, ca. 200 Zeilen. Die Werte bestehen aus 3ziffrigen Zahlen (010,011,100,271...). Diese Zahlen sollen durch andere 3ziffrige Zahlen ersetzt werden (z.B. 010 durch 001, 011 durch 004, 100 durch 200, 271 durch 355,...). Nun habe ich das Proble...
03.03.2009 11:26:38
Thread-ID: 1055879
19 Beiträge
Modul durch Makro löschen und ersetzen
Dietmar

Guten Morgen zusammen,
kann mir jemand einen Tipp geben?
Ich suche nach einer Möglichkeit, ein kompletes Makro-Modul per Makro zu löschen und durch ein neues Makro (mit exakt dem gleichen Namen) zu ersetzen.
Eine inhaltliche Änderung eines Makros per Makro erscheint mir zu schwierig...
26.03.2009 01:28:12
Thread-ID: 1064062
3 Beiträge
Anzeige
Suchen und ersetzen per Makro...?
Horst

Hey !
Ich möchte Tabellenblätter kopieren und während oder nach dem kopieren des Tabellenblattes im kopierten Tabellenblatt eine Volltextsuche mit Textersetzung durchführen.
Kann mir jemand dazu einen Tipp geben?
Und gibt es zur Suche/Ersetzen eine bessere Variant als die aus der Ma...
02.04.2009 11:43:43
Thread-ID: 1066267
3 Beiträge
Anzeige
VBA Suchen/Ersetzen inkl. Anzahl der Ersetzungen
Randolf

Hallo Excel-Gemeinde,
ich habe folgende Aufgabenstellung? Mit der normalen Suchen & Ersetzen-Funktion wird im Anschluss die Anzahl der Ersetzungen mitgeteilt. So weit so gut. Ich habe jetzt das Makro aufgezeichnet und es normal ablaufen lassen. Aber es kommt keine Anzahl der Ersetzungen...
14.05.2009 11:54:34
Thread-ID: 1075089
7 Beiträge
suchen und ersetzen
Detlef

Hallo alle zusammen,
ich habe mehrere Arbeitsblätter in denen ich immer den Inhalt einer Spalte überprüfe. Dabei wird der jeweilige Inhalt der Zelle mit dem Inhalt eines KorrekturArbeitsblattes verglichen. Wird der Zellinhalt gefunden, wird er ersetzt.
Im Moment mache ich das mit folgen...
04.06.2009 14:35:31
Thread-ID: 1079155
5 Beiträge
Marko zum Suchen und Ersetzen von Listenbegriffen
Listenbegriffen

Hallo Forum!
Ich verzweifele gerade (seit 2 Tagen) an einem Makro, das irgendwie nicht laufen will. Dabei klang es eigentlich relativ einfach.
Also, ich habe diverse Tabellenblätter, von denen das eine z.B. „BA2“ und ein anderes „Datenbank“ heißt. In „BA2“ stehen in Spalte A ab Zeile 5...
08.09.2009 11:56:07
Thread-ID: 1101193
7 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Schnelleres Makro für Suchen und Ersetzen in Excel

Schritt-für-Schritt-Anleitung

Um ein schnelleres Makro zum Suchen und Ersetzen in Excel zu erstellen, befolge diese Schritte:

  1. Öffne den VBA-Editor: Drücke ALT + F11.

  2. Erstelle ein neues Modul: Klicke mit der rechten Maustaste auf "VBAProject (dein Dokumentname)" > Einfügen > Modul.

  3. Füge den folgenden Code ein:

    Sub Schnelleres_Suchen_Ersetzen()
   Dim strIn As String, strOut As String
   strOut = "15200" 'zu ersetzender String
   strIn = "43900" 'einzufügender String

   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual

   Cells.Replace What:=strOut, Replacement:=strIn, LookAt:=xlPart, _
   SearchFormat:=False, ReplaceFormat:=False

   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

  4. Schließe den VBA-Editor und gehe zurück zu Excel.

  5. Wähle den Bereich aus, in dem Du nach Werten suchen möchtest.

  6. Führe das Makro aus: Drücke ALT + F8, wähle Schnelleres_Suchen_Ersetzen und klicke auf Ausführen.

Häufige Fehler und Lösungen

  • Fehler: "Falsche Anzahl an Argumenten oder ungültige Zuweisung zu einer Eigenschaft"

    • Lösung: Stelle sicher, dass der Bereich, den Du ersetzt, tatsächlich Formeln enthält. Verwende Selection.SpecialCells(xlCellTypeFormulas) um nur markierte Zellen mit Formeln zu bearbeiten.

  • Fehler: Laufzeitfehler bei Verwendung von Selection.Replace

    • Lösung: Prüfe, ob das zu ersetzende Element tatsächlich im markierten Bereich vorhanden ist.

  • Fehler: Makro bleibt hängen

    • Lösung: Überprüfe, ob Du Formeln mit externen Bezügen verwendest. Diese können die Ausführung des Makros verlangsamen.

Alternative Methoden

  1. Excel-Funktion "Suchen und Ersetzen":

    • Gehe zu Start > Suchen und Auswählen > Ersetzen. Dies ist die einfachste Methode, um Werte schnell zu ersetzen, allerdings nicht so automatisiert wie ein Makro.

  2. Verwende Arrays:

    • Ein Makro, das Arrays verwendet, kann schneller sein, da es die Neuberechnung der Formeln vermeidet, während die Werte ersetzt werden. Hier ein Beispiel:
    Sub Suchen_Ersetzen_Array()
   Dim arrTmp
   arrTmp = Selection.SpecialCells(xlCellTypeFormulas).Formula

   Dim i As Long
   For i = 1 To UBound(arrTmp)
       arrTmp(i) = Replace(arrTmp(i), "15200", "43900")
   Next i

   Selection.SpecialCells(xlCellTypeFormulas).Formula = arrTmp
End Sub

Praktische Beispiele

  1. Einfaches Ersetzen:

    • Wenn Du den Text "Tabelle3" in "Tabelle2" ändern möchtest, kannst Du das folgende Makro verwenden:
    Sub Ersetzen_Tabelle()
   Cells.Replace What:="Tabelle3", Replacement:="Tabelle2", LookAt:=xlPart
End Sub

  2. Ersetzen in markierten Zellen:

    • Um nur in markierten Zellen zu ersetzen, nutze:
    Sub Ersetzen_in_markierten_Zellen()
   Selection.Replace What:="15200", Replacement:="43900", LookAt:=xlPart
End Sub

Tipps für Profis

  • Nutze Application.Calculation = xlCalculationManual: Dies kann die Leistung erheblich steigern, indem Du die automatische Neuberechnung während des Ersetzens ausschaltest.
  • Verwende Application.ScreenUpdating = False: Damit wird das Flackern des Bildschirms reduziert und die Ausführung beschleunigt.
  • Teste Dein Makro mit verschiedenen Datensätzen, um die Leistung zu optimieren und sicherzustellen, dass es unter verschiedenen Bedingungen funktioniert.

FAQ: Häufige Fragen

1. Warum bleibt mein Makro hängen? Es könnte sein, dass Du versuchst, in einem Bereich ohne Formeln zu arbeiten. Stelle sicher, dass der markierte Bereich tatsächlich Formeln enthält.

2. Wie kann ich nur in markierten Zellen ersetzen? Verwende die Selection.Replace Methode, um sicherzustellen, dass nur die aktuell ausgewählten Zellen betroffen sind.

3. Was ist der Unterschied zwischen Replace und Substitute in VBA? Replace ersetzt Text in einer Zelle, während Substitute eine spezifische Instanz eines Textes in einem String ersetzt.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige