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

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

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

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
Anzeige
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

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
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

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

324 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige