Discussion:
VBA: String in einer Excel-Zelle suchen und ersetzen: Einschränkungen?
(zu alt für eine Antwort)
Leslie Anne
2006-06-10 11:28:27 UTC
Permalink
Liebe NG-Teilnehmer,

in einer größeren Excel-Tabelle möchte ich mit VBA code eine
Zeichenfolge suchen und ersetzen. Die Zeichenfolge taucht
innerhalb der jeweiligen Zelle mehrmals auf.

Folgendes Makro habe ich dafür geschrieben:

Option Explicit
Sub ZeichSuE()

Dim strZ As String
Dim Cll As Range
Dim objZ As Range

strZ = "Status"

For Each Cll In ActiveWorkbook.Worksheets(1).Cells
Set objZ =ActiveWorkbook.Worksheets(1).Cells.Find _
(What:=strZ, LookAt:=xlPart)
If objZ Is Nothing Then

MsgBox "Keinen weiteren Eintrag"
Exit Sub
Else
objZ.Select
ActiveCell.Replace _
What:=strZ, Replacement:="Datum", LookAt:=xlPart
End If
Next Cll

Set objZ = Nothing
Set Cll = Nothing
End Sub

Das Makro läuft nicht zu Ende, sondern "bleibt hängen" bei der
ersten Zelle in meiner Tabelle mit einem Inhalt von 1039 Zeichen.
Bei einer Zelle mit einem Inhalt von 902 Zeichen läuft das Makro
noch.

Es scheint also, dass das Ersetzen nicht funktioniert, wenn der
Zelleninhalt eine bestimmt Anzahl von Zeichen hat (irgendwo
zwischen 902 und 1039 Zeichen muß die Grenze sein?).

Ich habe in der VBA-Excel-Hilfe gelesen, daß die maximale Anzahl
von Zeichen in einer Excel-Zelle 32.767 ist; in der Zelle selbst
kann nur 1.024 Zeichen gezeigt werden, aber in der Formelleiste
alle bis zum 32.767 Zeichen.

Ich habe aber nichts finden können, was darauf hinweist, dass
ActiveCell.Replace bzgl. der Anzahl von Zeichen in einer Zelle
beschränkt ist.

Was tue ich denn falsch???
Vielen Dank fürs Feedback im Voraus!!!

Leslie Anne
Solaiman Ghaus
2006-06-09 13:22:49 UTC
Permalink
Hallo Leslie,
Post by Leslie Anne
Liebe NG-Teilnehmer,
in einer größeren Excel-Tabelle möchte ich mit VBA code eine
Zeichenfolge suchen und ersetzen. Die Zeichenfolge taucht
innerhalb der jeweiligen Zelle mehrmals auf.
Option Explicit
Sub ZeichSuE()
Dim strZ As String
Dim Cll As Range
Dim objZ As Range
strZ = "Status"
For Each Cll In ActiveWorkbook.Worksheets(1).Cells
Set objZ =ActiveWorkbook.Worksheets(1).Cells.Find _
(What:=strZ, LookAt:=xlPart)
If objZ Is Nothing Then
MsgBox "Keinen weiteren Eintrag"
Exit Sub
Else
objZ.Select
ActiveCell.Replace _
What:=strZ, Replacement:="Datum", LookAt:=xlPart
End If
Next Cll
Set objZ = Nothing
Set Cll = Nothing
End Sub
Ein Arbeitsblatt hat 65536 Zeilen und 256 Spalten wie du es sicherlich
weißt und das macht insgesamt 16777216 Zellen. Also mit For Each Cell
würde der Code ewig brauchen, um alle Zellen des Arbeitsblattes
abzuarbeiten. Deshalb würde ich den Suchbereich auf den tatsächlichen zu
suchenden Bereich einschränken.
Post by Leslie Anne
Das Makro läuft nicht zu Ende, sondern "bleibt hängen" bei der
ersten Zelle in meiner Tabelle mit einem Inhalt von 1039 Zeichen.
Bei einer Zelle mit einem Inhalt von 902 Zeichen läuft das Makro
noch.
Es scheint also, dass das Ersetzen nicht funktioniert, wenn der
Zelleninhalt eine bestimmt Anzahl von Zeichen hat (irgendwo
zwischen 902 und 1039 Zeichen muß die Grenze sein?).
Die Grenze hierzu ist max 910 Zeichen!
Post by Leslie Anne
Ich habe in der VBA-Excel-Hilfe gelesen, daß die maximale Anzahl
von Zeichen in einer Excel-Zelle 32.767 ist; in der Zelle selbst
kann nur 1.024 Zeichen gezeigt werden, aber in der Formelleiste
alle bis zum 32.767 Zeichen.
Das sind Grenzen zum Abspeichern bzw. Darstellung von Daten. Bei der
Verarbeitung der Daten per Cells.Replace gilt aber die Grenze von 910
Zeichen.
Post by Leslie Anne
Ich habe aber nichts finden können, was darauf hinweist, dass
ActiveCell.Replace bzgl. der Anzahl von Zeichen in einer Zelle
beschränkt ist.
s. o.
Post by Leslie Anne
Was tue ich denn falsch???
Ich würde es folgendermaßen lösen:

'---------------------------------------------------------------------
Sub Ersetzen()
Dim Suchtext, Ersatztext
Dim Bereich As Range
Dim Suche As Range
Dim i As Long

Suchtext = "Status"
Ersatztext = "Datum"

Set Bereich = Range("A1:L1") 'zu suchenden Bereich festlegen

Set Suche = Bereich.Find(what:=Suchtext, LookIn:=xlFormulas, LookAt:=xlPart)

If Suche Is Nothing Then
MsgBox "Keine übereinstimmende Daten gefunden!"
Else
Application.ScreenUpdating = False

Do
i = i + 1
Suche.Value = Replace(Suche.Value, Suchtext, Ersatztext)
Set Suche = Bereich.FindNext(Suche)
Loop Until Suche Is Nothing

MsgBox "Es wurden " & i & " Ersetzungen durchgeführt!"
End If
End Sub
'---------------------------------------------------------------------
Post by Leslie Anne
Vielen Dank fürs Feedback im Voraus!!!
Hoffe, damit ist die Frage beantwortet.
Post by Leslie Anne
Leslie Anne
Gruß
Solaiman
Leslie Anne
2006-06-11 09:39:21 UTC
Permalink
Hallo Solaiman,

Vielen Dank für Deine zügige Antwort. Ich brauchte Zeit, um Deine
und Wolfgangs Feedback auszuprobieren.

Folgendes habe ich festgestellt:

Mein Problem war nicht nur die Anzahl der Zeichen in der jeweiligen
Zelle, sondern auch, daß die Cells.Find-Methode ohne die Angabe
"MatchCase:=True" bei einer Zelle "hing", die 1039 Zeichen UND
"status" beinhaltet.
Eigentlich habe ich erwartet, daß die kleingeschriebenen Instanzen
von "Status" auch mit "Datum" ersetzt werden, da die Voreinstellung
von "MatchCase" "False" ist. Das war aber nicht der Fall. Statt
dessen blieb das Makro einfach bei der betreffenden Zelle stehen.

Mit der Angabe "MatchCase:=True" ist aber weder Dein noch Mein Makro
zu Ende gelaufen (wegen der Anzahl der Zeichen in manchen Zellen),
wohl aber Wolfgangs. Wolfgangs Makro ist auch dann
weiter gelaufen, obwohl eine Zelle sowohl "status" also auch
"Status" UND über 1380 Zeichen hat. Probetabelle hatte 1000
Datenzeilen
und 6 Spalten, und es wurden 360 Ersetzungen durchgeführt.

Ich habe Wolfgangs Makro für meinen Zwecken mit Deinem
Counter-Codezeile (i = i+1) erweitert.

Deine Empfehlung, den Suchbereich einzuschränken, habe ich
übernommen, da man sonst ein Speicherproblem kriegt.

Das Makro in der neuesten Version (mit all unseren Modifizierungen)
habe ich in meiner Antwort auf Wolfgangs Beitrag hinzugefügt.

Nochmals vielen Dank und
Schöne Grüße!
Leslie
Solaiman Ghaus
2006-06-11 12:28:37 UTC
Permalink
Hallo Leslie,
Post by Leslie Anne
Hallo Solaiman,
Vielen Dank für Deine zügige Antwort. Ich brauchte Zeit, um Deine
und Wolfgangs Feedback auszuprobieren.
gern geschehen, kein Problem.
Post by Leslie Anne
Mein Problem war nicht nur die Anzahl der Zeichen in der jeweiligen
Zelle, sondern auch, daß die Cells.Find-Methode ohne die Angabe
"MatchCase:=True" bei einer Zelle "hing", die 1039 Zeichen UND
"status" beinhaltet.
Das kann ich nicht nachvollziehen. Ich habe damit Zellen mit über 1500 -
9300 Zeichen durchsucht, hat alles reibungslos geklappt - auch mit
MatchCase True/False.
Post by Leslie Anne
Eigentlich habe ich erwartet, daß die kleingeschriebenen Instanzen
von "Status" auch mit "Datum" ersetzt werden, da die Voreinstellung
von "MatchCase" "False" ist. Das war aber nicht der Fall. Statt
dessen blieb das Makro einfach bei der betreffenden Zelle stehen.
Die Ersetzung findet natürlich durch Replace statt und Replace (in
meinem Code - nicht Cells.Replace) hat statt MatchCase den Parameter
vbCompareMethode. Standardeinstellung ist vbCompareMethode =
vbBinaryCompare, was gleichzusetzen ist mit "MatchCase:=True"!

Wenn Groß-/Keleinschreibung nicht beachtet werden soll, dann
vbTextCompare als Parameter an Replace() übergeben
Post by Leslie Anne
Mit der Angabe "MatchCase:=True" ist aber weder Dein noch Mein Makro
zu Ende gelaufen (wegen der Anzahl der Zeichen in manchen Zellen),
wohl aber Wolfgangs. Wolfgangs Makro ist auch dann
weiter gelaufen, obwohl eine Zelle sowohl "status" also auch
"Status" UND über 1380 Zeichen hat. Probetabelle hatte 1000
Datenzeilen
und 6 Spalten, und es wurden 360 Ersetzungen durchgeführt.
Wie oben bereits erwähnt, ich habe es in Excel 2003 im Bereich A1:O1100
mit Zellen über 1500-9300 Zeichenlänge und Groß-/Keleinschreibung
durchprobiert. Alles ist ohne Problem durchgelaufen.
Post by Leslie Anne
Ich habe Wolfgangs Makro für meinen Zwecken mit Deinem
Counter-Codezeile (i = i+1) erweitert.
Deine Empfehlung, den Suchbereich einzuschränken, habe ich
übernommen, da man sonst ein Speicherproblem kriegt.
Darüber freut sich dein PC ;-)
Post by Leslie Anne
Das Makro in der neuesten Version (mit all unseren Modifizierungen)
habe ich in meiner Antwort auf Wolfgangs Beitrag hinzugefügt.
Hier nochmal der Vollständigkeit halber der angepasste Code:
'---------------------------------------------------------------------
Sub Ersetzen()
Dim Suchtext, Ersatztext
Dim Bereich As Range
Dim Suche As Range
Dim i As Long

Suchtext = "sTAtuS"
Ersatztext = "Datum"

Set Bereich = Range("A1:O1100") 'zu suchenden Bereich festlegen

Set Suche = Bereich.Find(What:=Suchtext, LookIn:=xlFormulas,
LookAt:=xlPart, MatchCase:=False)

If Suche Is Nothing Then
MsgBox "Keine übereinstimmende Daten gefunden!"
Else
Application.ScreenUpdating = False

Do
i = i + 1
Application.StatusBar = "Ersetzung in: " & Suche.Address

Suche.Value = Replace(Suche.Value, Suchtext, Ersatztext, , ,
vbTextCompare)
Set Suche = Bereich.FindNext(Suche)
Loop Until Suche Is Nothing

MsgBox "Es wurden " & i & " Ersetzungen durchgeführt!"
End If

Application.StatusBar = False
Set Bereich = Nothing
Set Suche = Nothing
End Sub
'---------------------------------------------------------------------
Post by Leslie Anne
Nochmals vielen Dank und
Schöne Grüße!
Leslie
Viele Grüße
Solaiman
Leslie Anne
2006-06-12 08:57:19 UTC
Permalink
Hallo Solaiman,
Post by Solaiman Ghaus
Hallo Leslie,
Post by Leslie Anne
Hallo Solaiman,
[...]
Post by Solaiman Ghaus
Post by Leslie Anne
Mein Problem war nicht nur die Anzahl der Zeichen in der
jeweiligen
Post by Solaiman Ghaus
Post by Leslie Anne
Zelle, sondern auch, daß die Cells.Find-Methode ohne die Angabe
"MatchCase:=True" bei einer Zelle "hing", die 1039 Zeichen UND
"status" beinhaltet.
Das kann ich nicht nachvollziehen. Ich habe damit Zellen mit über 1500 -
9300 Zeichen durchsucht, hat alles reibungslos geklappt - auch mit
MatchCase True/False.
Mit "Mein Problem" meinte ich das mit meinem ursprünglichen Makro.
Post by Solaiman Ghaus
Post by Leslie Anne
Eigentlich habe ich erwartet, daß die kleingeschriebenen
Instanzen
Post by Solaiman Ghaus
Post by Leslie Anne
von "Status" auch mit "Datum" ersetzt werden, da die
Voreinstellung
Post by Solaiman Ghaus
Post by Leslie Anne
von "MatchCase" "False" ist. Das war aber nicht der Fall. Statt
dessen blieb das Makro einfach bei der betreffenden Zelle
stehen.

Bezieht sich auch auf mein ursprüngliches Makro.
Post by Solaiman Ghaus
Die Ersetzung findet natürlich durch Replace statt und Replace (in
meinem Code - nicht Cells.Replace) hat statt MatchCase den
Parameter
Post by Solaiman Ghaus
vbCompareMethode. Standardeinstellung ist vbCompareMethode =
vbBinaryCompare, was gleichzusetzen ist mit "MatchCase:=True"!
Wenn Groß-/Keleinschreibung nicht beachtet werden soll, dann
vbTextCompare als Parameter an Replace() übergeben
Post by Leslie Anne
Mit der Angabe "MatchCase:=True" ist aber weder Dein noch Mein Makro
zu Ende gelaufen (wegen der Anzahl der Zeichen in manchen
Zellen),
[...]
In Bezug auf Dein Makro stimmt meine Aussage nicht ganz.

Ich habe noch einmal Dein erstes Makro ausprobiert und es ist
weiterhin nicht zu Ende gelaufen (die Zuordnung von "Bereich" ist an
meiner Tabelle angepaßt). Mit einem "Stop" vor "Loop Until" konnte
ich feststellen, daß die Ersetzungen tatsächlich stattfanden. Aber,
das Makro lief einfach weiter, obwohl der Bereich längst bearbeitet
war. Dies konnte ich beim i-Wert im Lokalfenster beobachten.

Aber Dein modifiziertes Code (unten) hat bei mir funktioniert! Siehe
bitte meine letzte Frage am Ende.
[...]
Post by Solaiman Ghaus
Sub Ersetzen()
Dim Suchtext, Ersatztext
Dim Bereich As Range
Dim Suche As Range
Dim i As Long
Suchtext = "sTAtuS"
Ersatztext = "Datum"
Set Bereich = Range("A1:O1100") 'zu suchenden Bereich festlegen
Set Suche = Bereich.Find(What:=Suchtext, LookIn:=xlFormulas,
LookAt:=xlPart, MatchCase:=False)
If Suche Is Nothing Then
MsgBox "Keine übereinstimmende Daten gefunden!"
Else
Application.ScreenUpdating = False
Do
i = i + 1
Application.StatusBar = "Ersetzung in: " & Suche.Address
Suche.Value = Replace(Suche.Value, Suchtext, Ersatztext, , ,
vbTextCompare)
Set Suche = Bereich.FindNext(Suche)
Loop Until Suche Is Nothing
MsgBox "Es wurden " & i & " Ersetzungen durchgeführt!"
End If
Application.StatusBar = False
Set Bereich = Nothing
Set Suche = Nothing
End Sub
Um Groß-/Kleinschreibung zu beachten, habe ich bei Set Suche den
Wert von MatchCase: auf "True" gesetzt und
bei Suche.Value = Replace, vbBinaryCompare eingesetzt. Das Makro
findet "keine übereinstimmende Daten".

Wie soll ich Dein neues Makro ändern, damit Groß-/Kleinschreibung
beachtet wird?

Schöne Grüße
Leslie
Solaiman Ghaus
2006-06-12 10:50:45 UTC
Permalink
Hallo Leslie,
Post by Leslie Anne
Hallo Solaiman,
In Bezug auf Dein Makro stimmt meine Aussage nicht ganz.
Ich habe noch einmal Dein erstes Makro ausprobiert und es ist
weiterhin nicht zu Ende gelaufen (die Zuordnung von "Bereich" ist an
meiner Tabelle angepaßt). Mit einem "Stop" vor "Loop Until" konnte
ich feststellen, daß die Ersetzungen tatsächlich stattfanden. Aber,
das Makro lief einfach weiter, obwohl der Bereich längst bearbeitet
war. Dies konnte ich beim i-Wert im Lokalfenster beobachten.
In meinem ersten Code wird bei "Set Suche = Bereich.Find" auf
Groß-/Kleinschreibung nicht geachtet, aber bei "Suche.Value = Replace"
sehr wohl. Deshalb werden Ersetzungen NUR für "Status" durchgeführt,
aber die anders geschriebenen wie "sTatus", "STAtus" usw. werden nicht
ersetzt. Die führt zu einer Endlosschleife, denn die Bedingung "Loop
Until Suche Is Nothing" wird nie erfüllt werden!
Post by Leslie Anne
Aber Dein modifiziertes Code (unten) hat bei mir funktioniert! Siehe
bitte meine letzte Frage am Ende.
Ist doch prima, freut mich.
Post by Leslie Anne
[...]
Um Groß-/Kleinschreibung zu beachten, habe ich bei Set Suche den
Wert von MatchCase: auf "True" gesetzt und
bei Suche.Value = Replace, vbBinaryCompare eingesetzt. Das Makro
findet "keine übereinstimmende Daten".
Wie soll ich Dein neues Makro ändern, damit Groß-/Kleinschreibung
beachtet wird?
Das hast du schon richtig gemacht, obwohl vbBinaryCompare brauchst du
nicht zwingend - es kann ruhig bei vbTextCompare bleiben, denn du
regelst nun die Groß-/Kleinschreibung in einer Ebene höher in Find().

Bist du dir Sicher, daß der Suchbegriff richtig geschrieben ist und es
ist auch in dem Bereich vorhanden? Mir ist gerade auch so ein Fehler
passiert - ich wollte nach "Status" suchen aber geschrieben hatte ich
"StatuS" ;-)
Post by Leslie Anne
Schöne Grüße
Leslie
Gruß
Solaiman
Leslie Anne
2006-06-13 09:54:43 UTC
Permalink
Hallo Solaiman,
Post by Solaiman Ghaus
Hallo Leslie,
[...]
Post by Solaiman Ghaus
Bist du dir Sicher, daß der Suchbegriff richtig geschrieben ist und es
ist auch in dem Bereich vorhanden? Mir ist gerade auch so ein
Fehler
Post by Solaiman Ghaus
passiert - ich wollte nach "Status" suchen aber geschrieben hatte ich
"StatuS" ;-)
[...]

Man, man, man - das war es - Makro läuft, ob ich Groß/Klein beachten
will, oder nicht. Okay.

ABER der i-Wert: wie Wolfgang bemerkt hat, ist er die Anzahl der
gefundenen Zellen. Was muß man machen, damit der i-Wert die Anzahl
der tatsächlichen Ersetzungen wiedergibt? Soweit ich recherchiert
habe, geht es mit CountIF auch nicht.

Ich danke für Dein Feedback im Voraus!
Leslie
Solaiman Ghaus
2006-06-13 10:31:27 UTC
Permalink
Hallo Leslie,
Post by Leslie Anne
Hallo Solaiman,
Man, man, man - das war es - Makro läuft, ob ich Groß/Klein beachten
will, oder nicht. Okay.
tja - kann sehr schnell passieren wie man sieht ;-) Aber freut mich, daß
es nun funktioniert.
Post by Leslie Anne
ABER der i-Wert: wie Wolfgang bemerkt hat, ist er die Anzahl der
gefundenen Zellen. Was muß man machen, damit der i-Wert die Anzahl
der tatsächlichen Ersetzungen wiedergibt? Soweit ich recherchiert
habe, geht es mit CountIF auch nicht.
Wenn du die Steuerung von Groß-/Kleinschreibung über Find also
MatchCase:=True/False machst und in Replace den Parameter immer auf
vbTextCompare lässt, dann wird "i" immer richtig gezählt, denn dann ist
Fund-Anzahl = Ersetzung-Anzahl!

Probier doch mal bitte meinen Code 1:1, dann wirst du es sehen.
Post by Leslie Anne
Ich danke für Dein Feedback im Voraus!
Leslie
Gruß
Solaiman
Leslie Anne
2006-06-13 18:07:26 UTC
Permalink
Post by Solaiman Ghaus
Hallo Leslie,
Post by Leslie Anne
Hallo Solaiman,
[...]
Post by Solaiman Ghaus
Post by Leslie Anne
ABER der i-Wert: wie Wolfgang bemerkt hat, ist er die Anzahl der
gefundenen Zellen. Was muß man machen, damit der i-Wert die
Anzahl
Post by Solaiman Ghaus
Post by Leslie Anne
der tatsächlichen Ersetzungen wiedergibt? Soweit ich
recherchiert
Post by Solaiman Ghaus
Post by Leslie Anne
habe, geht es mit CountIF auch nicht.
Wenn du die Steuerung von Groß-/Kleinschreibung über Find also
MatchCase:=True/False machst und in Replace den Parameter immer auf
vbTextCompare lässt, dann wird "i" immer richtig gezählt, denn dann ist
Fund-Anzahl = Ersetzung-Anzahl!
Probier doch mal bitte meinen Code 1:1, dann wirst du es sehen.
[...]
Ich bin mir nicht sicher, was Du mit "1:1" meinst. Auf jeden
Fall habe ich eine kleinere Probetabelle erstellt (A1:C3). Schön
überschaubar. "Status" erscheint mehrmals in einer gegebenen Zelle.
Leider wurde in keinem Fall (Kombinationen von MatchCase-Werte und
Replace vbTextCompare bzw. vbBinaryCompare) die Anzahl der
tatsächlichen Ersetzungen bei der "i"-Meldung angegeben. Nur die
Anzahl der Zellen. Ich habe geradezu gehofft, dass ich gestern etwas
übersehen hätte, aber das war nicht der Fall.

Wie gesagt, ich recherchiere, aber hab' noch nicht 'was gefunden,
zumindest nicht eine endgültige Antwort.
Ein Beitrag auf Englisch sah vielversprechend aus, ich vermute, man
könnte die dortige Funktion irgendwie modifizieren und irgendwie in
eine Do-Loop- oder eine For-Each-Struktur einbauen. Der Beitrag ist
"counting instances of a character within a string" in der "Excel
Programming" Newsgroup. Ich habe leider nicht mehr eine Verknüpfung
dazu.

Schöne Grüße
Leslie
Solaiman Ghaus
2006-06-14 00:39:58 UTC
Permalink
Hallo Leslie,
Post by Leslie Anne
Ich bin mir nicht sicher, was Du mit "1:1" meinst. Auf jeden
Fall habe ich eine kleinere Probetabelle erstellt (A1:C3). Schön
überschaubar. "Status" erscheint mehrmals in einer gegebenen Zelle.
Mit "eins zu eins" meinte ich, daß du vor der Modifizierung des Codes
den Code ein Mal probierst, um mögliche Fehler im Code fest zu stellen.
Denn nachher ist es für mich schwer nach zu vollziehen, wo und ob ein
Fehler auftritt, da du mit einem anderen Code arbeitest.

Das stimmt, ich hatte mehrfach Vorkommnissen in Zellen bei meinen
bisherigen Codes nicht berücksichtigt, aber nicht mehr lange ;-)
Post by Leslie Anne
Leider wurde in keinem Fall (Kombinationen von MatchCase-Werte und
Replace vbTextCompare bzw. vbBinaryCompare) die Anzahl der
tatsächlichen Ersetzungen bei der "i"-Meldung angegeben. Nur die
Anzahl der Zellen. Ich habe geradezu gehofft, dass ich gestern etwas
übersehen hätte, aber das war nicht der Fall.
War mein Fehler - ich hatte wie oben erwähnt mehrfachen Suchtext in
einer Zelle glatt vergessen zu berücksichtigen. Danke, daß du mich
darauf aufmerksam gemacht hast.
Post by Leslie Anne
Wie gesagt, ich recherchiere, aber hab' noch nicht 'was gefunden,
zumindest nicht eine endgültige Antwort.
Ein Beitrag auf Englisch sah vielversprechend aus, ich vermute, man
könnte die dortige Funktion irgendwie modifizieren und irgendwie in
eine Do-Loop- oder eine For-Each-Struktur einbauen. Der Beitrag ist
"counting instances of a character within a string" in der "Excel
Programming" Newsgroup. Ich habe leider nicht mehr eine Verknüpfung
dazu.
Es gibt mehrere Möglichkeiten, den Zähler entsprechend der tatsächlichen
Ersetzungen hoch zu zählen. Eine davon z. B. folgenden:

'--------------------------------------------------------------------------------------------------
Sub Ersetzen_V3()
Dim Suchtext As Variant
Dim Ersatztext As Variant
Dim Bereich As Range
Dim Suche As Range
Dim LSuchtext As Integer 'Länge vom Suchtext
Dim LGesamt As Integer 'Länge vom genazen Text der Fundzelle
Dim LRest As Integer 'Wie LGesamt, aber exklusive Suchtext
Dim FundAnzahl As Integer
Dim i As Long

Suchtext = "sTAtuS"
Ersatztext = "Datum"
LSuchtext = Len(Suchtext)

Set Bereich = Range("A1:O100") 'zu suchenden Bereich festlegen

Set Suche = Bereich.Find(What:=Suchtext, LookIn:=xlFormulas,
LookAt:=xlPart, MatchCase:=False)

If Suche Is Nothing Then
MsgBox "Keine übereinstimmende Daten gefunden!"
Else
Application.ScreenUpdating = False

Do
LGesamt = Len(Suche.Value)
LRest = Len(Replace(Suche.Value, Suchtext, "", , , vbTextCompare))

FundAnzahl = (LGesamt - LRest) / LSuchtext

i = i + FundAnzahl
Application.StatusBar = "Ersetzung in: " & Suche.Address

Suche.Value = Replace(Suche.Value, Suchtext, Ersatztext, , ,
vbTextCompare)
Set Suche = Bereich.FindNext(Suche)
Loop Until Suche Is Nothing

Application.ScreenUpdating = True
MsgBox "Es wurden " & i & " Ersetzungen durchgeführt!"
End If

Application.StatusBar = False
Set Bereich = Nothing
Set Suche = Nothing
End Sub
'--------------------------------------------------------------------------------------------------

Beim Testen ist mir aufgefallen, daß wir über "Ganzes Wort
Suchen/Ersetzen" nicht gesprochen haben.
Was soll denn z. B. bei "sTAtuSsTAtuS" (2x) passieren? Oder z. B. du
suchst eigentlich nach "Excel" aber es wird auch "Excellent"
gefunden/ersetzt!

So - jetzt muß ich aber gehen ;-)
Post by Leslie Anne
Schöne Grüße
Leslie
Gruß
Solaiman
Leslie Anne
2006-06-15 09:35:03 UTC
Permalink
Hallo Solaiman,
Post by Solaiman Ghaus
Hallo Leslie,
[...]
Post by Solaiman Ghaus
Es gibt mehrere Möglichkeiten, den Zähler entsprechend der
tatsächlichen
'-------------------------------------------------------------------
Post by Solaiman Ghaus
Sub Ersetzen_V3()
Dim Suchtext As Variant
Dim Ersatztext As Variant
Dim Bereich As Range
Dim Suche As Range
Dim LSuchtext As Integer 'Länge vom Suchtext
Dim LGesamt As Integer 'Länge vom genazen Text der Fundzelle
Dim LRest As Integer 'Wie LGesamt, aber exklusive Suchtext
Dim FundAnzahl As Integer
Dim i As Long
Suchtext = "sTAtuS"
Ersatztext = "Datum"
LSuchtext = Len(Suchtext)
Set Bereich = Range("A1:O100") 'zu suchenden Bereich festlegen
Set Suche = Bereich.Find(What:=Suchtext, LookIn:=xlFormulas,
LookAt:=xlPart, MatchCase:=False)
If Suche Is Nothing Then
MsgBox "Keine übereinstimmende Daten gefunden!"
Else
Application.ScreenUpdating = False
Do
LGesamt = Len(Suche.Value)
LRest = Len(Replace(Suche.Value, Suchtext, "", , ,
vbTextCompare))
Post by Solaiman Ghaus
FundAnzahl = (LGesamt - LRest) / LSuchtext
i = i + FundAnzahl
Application.StatusBar = "Ersetzung in: " & Suche.Address
Suche.Value = Replace(Suche.Value, Suchtext, Ersatztext, , ,
vbTextCompare)
Set Suche = Bereich.FindNext(Suche)
Loop Until Suche Is Nothing
Application.ScreenUpdating = True
MsgBox "Es wurden " & i & " Ersetzungen durchgeführt!"
End If
Application.StatusBar = False
Set Bereich = Nothing
Set Suche = Nothing
End Sub
'-------------------------------------------------------------------

Funtastisch! Sub Ersetzen_V3() hat bei mir auch funktioniert mit
MatchCase:=True zusammen mit vbBinaryCompare.
Post by Solaiman Ghaus
Beim Testen ist mir aufgefallen, daß wir über "Ganzes Wort
Suchen/Ersetzen" nicht gesprochen haben.
Was soll denn z. B. bei "sTAtuSsTAtuS" (2x) passieren? Oder z. B. du
suchst eigentlich nach "Excel" aber es wird auch "Excellent"
gefunden/ersetzt!
Oh weh! Ich habe in der Probetabelle "Statusmacher" und "xStatus"
eingesetzt, und - Du hast Recht - dies wurde auch ersetzt (mit
MatchCase:=True und vbBinaryCompare).

Mein Recherchieren ergab bisher nichts diesbezüglich.

Die einzige Idee, die ich bis jetzt hatte, ist nicht "VBA
ausgereift", nämlich im Else-Block vor Do ein IF-Then Block
einsetzen, im folgenden Sinne: If vor Suchtext nicht nichts (nichts
= Suchtext steht im Anfang der Zelle) oder Chr$(32) oder
ALT+Shift-Zeichen und nach Suchtext nicht nichts oder Chr$(32) oder
Chr$(44) oder Chr$(46) usw. steht, Then MsgBox "Keine
Übereinstimmung für das Wort Status", Exit Sub End If. So 'was in
der Art. Ich grübele weiter.

Aber eventuell hast Du das Problem bereits gelöst?

Nochmals vielen Dank für Sub_Ersetzen_V3()!

Schöne Grüße
Leslie
Solaiman Ghaus
2006-06-15 22:32:01 UTC
Permalink
Hallo Leslie,
Post by Leslie Anne
Hallo Solaiman,
Funtastisch! Sub Ersetzen_V3() hat bei mir auch funktioniert mit
MatchCase:=True zusammen mit vbBinaryCompare.
freut mich, schön.
Post by Leslie Anne
Oh weh! Ich habe in der Probetabelle "Statusmacher" und "xStatus"
eingesetzt, und - Du hast Recht - dies wurde auch ersetzt (mit
MatchCase:=True und vbBinaryCompare).
Mit MatchCase:=True und vbBinaryCompare dürften sie eigentlich nicht
ersetzt werden, bei MatchCase:=False und vbTextCompare aber schon.
Post by Leslie Anne
Mein Recherchieren ergab bisher nichts diesbezüglich.
Die einzige Idee, die ich bis jetzt hatte, ist nicht "VBA
ausgereift", nämlich im Else-Block vor Do ein IF-Then Block
einsetzen, im folgenden Sinne: If vor Suchtext nicht nichts (nichts
= Suchtext steht im Anfang der Zelle) oder Chr$(32) oder
ALT+Shift-Zeichen und nach Suchtext nicht nichts oder Chr$(32) oder
Chr$(44) oder Chr$(46) usw. steht, Then MsgBox "Keine
Übereinstimmung für das Wort Status", Exit Sub End If. So 'was in
der Art. Ich grübele weiter.
Wäre auch möglich, aber zu umständlich, da sehr viele Ausnahmen
berücksichtigt werden müssen.
Post by Leslie Anne
Aber eventuell hast Du das Problem bereits gelöst?
Na Logo ;-)

Da müssen wir mit regulären Ausdrücken arbeiten.

Hier kommt Ersetzen_V4:
'--------------------------------------------------------------------------------------------------
Sub Ersetzen_V4()
Dim Suchtext As Variant
Dim Ersatztext As Variant
Dim Bereich As Range
Dim Suche As Range
Dim Endadresse As String
Dim RegAusdruck As Object
Dim FundMatrix As Variant
Dim i As Long

Suchtext = "sTAtuS"
Ersatztext = "Datum"

Set Bereich = Range("A1:O1100") 'zu durchsuchenden Bereich festlegen

Set Suche = Bereich.Find(What:=Suchtext, LookIn:=xlFormulas,
LookAt:=xlPart, MatchCase:=False)

If Suche Is Nothing Then
MsgBox "Keine übereinstimmende Daten gefunden!"
Else
Application.ScreenUpdating = False
Set RegAusdruck = CreateObject("VBScript.RegExp")

Do
With RegAusdruck
.Global = True
.IgnoreCase = True
.Pattern = "\b" & Suchtext & "\b" 'Ganzes Wort suchen
Set FundMatrix = .Execute(Suche.Value)
Suche.Value = .Replace(Suche.Value, Ersatztext)

i = i + FundMatrix.Count
End With

If InStr(1, Suche.Value, Suchtext, vbTextCompare) > 0 And
Endadresse = "" Then
Endadresse = Suche.Address
End If

Application.StatusBar = "Ersetzung in: " & Suche.Address & " -
" & i

Set Suche = Bereich.FindNext(Suche)

On Error Resume Next
Loop Until Suche Is Nothing Or Suche.Address = Endadresse

Application.ScreenUpdating = True
Application.StatusBar = False
Set Suche = Nothing
Set RegAusdruck = Nothing
MsgBox "Es wurden " & i & " Ersetzungen durchgeführt!"
End If

Set Bereich = Nothing
End Sub
'--------------------------------------------------------------------------------------------------
Post by Leslie Anne
Nochmals vielen Dank für Sub_Ersetzen_V3()!
Gern geschehen. Freut mich, daß wir uns langsam aber sicher der
Endlösung nähern bzw. haben wir sie bereits durch V4 erreicht.
Post by Leslie Anne
Schöne Grüße
Leslie
Viele Grüße
Solaiman

Wolfgang Habernoll
2006-06-10 15:42:06 UTC
Permalink
Hallo Leslie

es scheint das sich XL bei mehr als 910 Zeichen in der Zelle sich anders
verhält. Um dein Problem zu lösen könntest du WECHSELN in VBA nehmen, das geht
auch über 910 Zeichen. Es sähe in deinem Code dann so aus.
Post by Leslie Anne
Snip
If objZ Is Nothing Then
MsgBox "Keinen weiteren Eintrag"
Exit Sub
Else

''ersetze deine 2 Zeilen durch diese. Now() ist das DATUM sonst "Datum"
objZ = Application.WorksheetFunction.Substitute(objZ, strZ, Now())

End If
Post by Leslie Anne
Snip
mfG
Wolfgang Habernoll
Post by Leslie Anne
Liebe NG-Teilnehmer,
in einer größeren Excel-Tabelle möchte ich mit VBA code eine
Zeichenfolge suchen und ersetzen. Die Zeichenfolge taucht
innerhalb der jeweiligen Zelle mehrmals auf.
Option Explicit
Sub ZeichSuE()
Dim strZ As String
Dim Cll As Range
Dim objZ As Range
strZ = "Status"
For Each Cll In ActiveWorkbook.Worksheets(1).Cells
Set objZ =ActiveWorkbook.Worksheets(1).Cells.Find _
(What:=strZ, LookAt:=xlPart)
If objZ Is Nothing Then
MsgBox "Keinen weiteren Eintrag"
Exit Sub
Else
objZ.Select
ActiveCell.Replace _
What:=strZ, Replacement:="Datum", LookAt:=xlPart
End If
Next Cll
Set objZ = Nothing
Set Cll = Nothing
End Sub
Das Makro läuft nicht zu Ende, sondern "bleibt hängen" bei der
ersten Zelle in meiner Tabelle mit einem Inhalt von 1039 Zeichen.
Bei einer Zelle mit einem Inhalt von 902 Zeichen läuft das Makro
noch.
Es scheint also, dass das Ersetzen nicht funktioniert, wenn der
Zelleninhalt eine bestimmt Anzahl von Zeichen hat (irgendwo
zwischen 902 und 1039 Zeichen muß die Grenze sein?).
Ich habe in der VBA-Excel-Hilfe gelesen, daß die maximale Anzahl
von Zeichen in einer Excel-Zelle 32.767 ist; in der Zelle selbst
kann nur 1.024 Zeichen gezeigt werden, aber in der Formelleiste
alle bis zum 32.767 Zeichen.
Ich habe aber nichts finden können, was darauf hinweist, dass
ActiveCell.Replace bzgl. der Anzahl von Zeichen in einer Zelle
beschränkt ist.
Was tue ich denn falsch???
Vielen Dank fürs Feedback im Voraus!!!
Leslie Anne
Leslie Anne
2006-06-11 09:39:41 UTC
Permalink
Hallo Wolfgang,

vielen Dank für Deine Antwort. Das Makro mit Deinen Modifizierungen
hat tatsächlich funktioniert!
Es wäre ein Albtraum gewesen, wenn ich die Tabelle wegen des
Überschreitens der 910-Zeichen-Grenze hätte ändern müssen.

Ich mußte aber die Angabe "MatchCase:=True" bei der
Cells.Find-Methode hinzufügen, da das Makro trotzdem stehen blieb,
wenn eine Zelle eine kleingeschriebene Instanz von "Status" hat. Mit
"MatchCase:=False" oder ohne Angaben funktioniert's nicht.
Will man also alle Instanzen ersetzen, egal ob groß- oder
kleingeschrieben, dann muß man das Makro mit den entsprechenden
MatchCase-Angaben 2x laufen lassen.
Für meine Zwecke habe ich Solaimans Codezeile für das Zählen der
Ersetzungen benutzt.

Hier das Makro in seiner jetzigen Form:

Option Explicit
Sub SuchErsetz()
Dim strZ As String
Dim Cll As Range
Dim objZ As Range
Dim i As Integer

strZ = "Status"
For Each Cll In ActiveWorkbook.Worksheets(1).Cells
Set objZ = ActiveWorkbook.Worksheets(1).Cells.Find _
(What:=strZ, MatchCase:=True, LookAt:=xlPart)

If objZ Is Nothing Then
GoTo Adios
Else
objZ = Application.WorksheetFunction.Substitute _
(objZ, strZ, "Datum")
objZ.Select
i = i + 1
End If

Next Cll
Adios:
MsgBox "Es wurden " & i & " Ersetzungen durchgeführt."
Set objZ = Nothing
Set Cll = Nothing
End Sub

Ich weiß nicht, bei wie vielen Datensätzen das Speicherproblem
auftaucht, aber zumindest bei 1000 Datenzeilen mit 6 Spalten
funktioniert's. Das ist schon eine Menge.

Danke schön noch mals!
Leslie
--------------------------------------------------------------------
Post by Solaiman Ghaus
Hallo Leslie
es scheint das sich XL bei mehr als 910 Zeichen in der Zelle sich anders
verhält. Um dein Problem zu lösen könntest du WECHSELN in VBA nehmen, das geht
auch über 910 Zeichen. Es sähe in deinem Code dann so aus.
Snip
If objZ Is Nothing Then
MsgBox "Keinen weiteren Eintrag"
Exit Sub
Else
''ersetze deine 2 Zeilen durch diese. Now() ist das DATUM sonst "Datum"
objZ = Application.WorksheetFunction.Substitute(objZ, strZ,
Now())
Post by Solaiman Ghaus
End If
Snip
mfG
Wolfgang Habernoll
Wolfgang Habernoll
2006-06-12 10:01:27 UTC
Permalink
Hallo Leslie

erst mal Danke für die Rückmeldung, ich sehe aber das du neue Probleme hast.
Ursprünglich war ja Groß/klein nicht zu beachten. Ich hatte auch das REPLACE
(was ja bei dir nicht klappte) nicht weiter beachtet und SUBSTITUTE
genommen. Leider wird hier nur bei *genauer* übereistimmung der Texte
getauscht. Das mein(dein) Makro durchläuft liegt daran das du es deine Tabelle
mit For/Each durchläufst, dabei werden "Status" getauscht , "status" nicht und
fertig. In Solamin's altem Makro wird immer wieder "status" gefunden weil er
immer neu sucht (kein Ende).
Solamins neues funktioniert, weil er durch den vbTextCompare Parameter auch
"status" ersetzt. Für welche Makro/Schleife du dich entscheidest ist egal, ich
empfehle aber zum ersetzen Solamins Zeile zu nehmen

Suche.Value = Replace(Suche.Value, Suchtext, Ersatztext, , , vbTextCompare)

!! du verrennst dich mit bei Find mit MatchCase:=True das *muß*
MatchCase:=FALSE sein
damit jede Zelle mit Status ob groß oder klein auch gefunden wird.

eine Bemerkung zu "Es wurden " & i & " Ersetzungen durchgeführt." i > ist
aber nur die Anzahl der gefundenen Zellen, nicht die gefundenen Worte die
ersetzt wurden.

noch mal dein Makro abgeändert, Frage wozu das "objZ.Select" siehst du es
gerne wenn der Rechnet arbeitet ;-)

Sub SuchErsetz()
Dim strZ As String
Dim Cll As Range
Dim objZ As Range
Dim i As Integer

strZ = "Status"
For Each Cll In ActiveWorkbook.Worksheets(1).Cells
Set objZ = ActiveWorkbook.Worksheets(1).Cells.Find _
(What:=strZ, MatchCase:=False, LookAt:=xlPart)

If objZ Is Nothing Then
GoTo Adios
Else
Suche.Value = Replace(Suche.Value, Suchtext, _
Ersatztext, , , vbTextCompare)
objZ.Select
i = i + 1
End If

Next Cll
Adios:
MsgBox "Es wurden " & i & " Ersetzungen durchgeführt."
Set objZ = Nothing
Set Cll = Nothing
End Sub

mfG
Wolfgang Habernoll
Post by Leslie Anne
Hallo Wolfgang,
vielen Dank für Deine Antwort. Das Makro mit Deinen Modifizierungen
hat tatsächlich funktioniert!
Es wäre ein Albtraum gewesen, wenn ich die Tabelle wegen des
Überschreitens der 910-Zeichen-Grenze hätte ändern müssen.
Ich mußte aber die Angabe "MatchCase:=True" bei der
Cells.Find-Methode hinzufügen, da das Makro trotzdem stehen blieb,
wenn eine Zelle eine kleingeschriebene Instanz von "Status" hat. Mit
"MatchCase:=False" oder ohne Angaben funktioniert's nicht.
Will man also alle Instanzen ersetzen, egal ob groß- oder
kleingeschrieben, dann muß man das Makro mit den entsprechenden
MatchCase-Angaben 2x laufen lassen.
Für meine Zwecke habe ich Solaimans Codezeile für das Zählen der
Ersetzungen benutzt.
Option Explicit
Sub SuchErsetz()
Dim strZ As String
Dim Cll As Range
Dim objZ As Range
Dim i As Integer
strZ = "Status"
For Each Cll In ActiveWorkbook.Worksheets(1).Cells
Set objZ = ActiveWorkbook.Worksheets(1).Cells.Find _
(What:=strZ, MatchCase:=True, LookAt:=xlPart)
If objZ Is Nothing Then
GoTo Adios
Else
objZ = Application.WorksheetFunction.Substitute _
(objZ, strZ, "Datum")
objZ.Select
i = i + 1
End If
Next Cll
MsgBox "Es wurden " & i & " Ersetzungen durchgeführt."
Set objZ = Nothing
Set Cll = Nothing
End Sub
Ich weiß nicht, bei wie vielen Datensätzen das Speicherproblem
auftaucht, aber zumindest bei 1000 Datenzeilen mit 6 Spalten
funktioniert's. Das ist schon eine Menge.
Danke schön noch mals!
Leslie
--------------------------------------------------------------------
Post by Solaiman Ghaus
Hallo Leslie
es scheint das sich XL bei mehr als 910 Zeichen in der Zelle sich
anders
Post by Solaiman Ghaus
verhält. Um dein Problem zu lösen könntest du WECHSELN in VBA
nehmen, das geht
Post by Solaiman Ghaus
auch über 910 Zeichen. Es sähe in deinem Code dann so aus.
Snip
If objZ Is Nothing Then
MsgBox "Keinen weiteren Eintrag"
Exit Sub
Else
''ersetze deine 2 Zeilen durch diese. Now() ist das DATUM sonst
"Datum"
Post by Solaiman Ghaus
objZ = Application.WorksheetFunction.Substitute(objZ, strZ,
Now())
Post by Solaiman Ghaus
End If
Snip
mfG
Wolfgang Habernoll
Leslie Anne
2006-06-13 09:54:23 UTC
Permalink
Hallo Wolfgang,
Post by Solaiman Ghaus
Hallo Leslie
[...]
Post by Solaiman Ghaus
Ich hatte auch das REPLACE
(was ja bei dir nicht klappte) nicht weiter beachtet und
SUBSTITUTE
Post by Solaiman Ghaus
genommen. Leider wird hier nur bei *genauer* übereistimmung der Texte
getauscht. Das mein(dein) Makro durchläuft liegt daran das du es deine Tabelle
mit For/Each durchläufst, dabei werden "Status" getauscht ,
"status" nicht und
Post by Solaiman Ghaus
fertig.
Das ist okay. Makro sehr nützlich wegen For/Each-Struktur.

[...]
Post by Solaiman Ghaus
!! du verrennst dich mit bei Find mit MatchCase:=True das *muß*
MatchCase:=FALSE sein
damit jede Zelle mit Status ob groß oder klein auch gefunden
wird.

Ja, das Problem war im diesem Fall "StatuS", statt "Status", bzw.
"status." Der Teufel steckt bekanntlich im Detail!
Hast Du bereits sicherlich Solaimans Beitrag gelesen.
Post by Solaiman Ghaus
eine Bemerkung zu "Es wurden " & i & " Ersetzungen durchgeführt."
i > ist
Post by Solaiman Ghaus
aber nur die Anzahl der gefundenen Zellen, nicht die gefundenen Worte die
ersetzt wurden.
Anzahl der gefundenen Worte wäre besser für meine Zwecke, aber WIE?
Post by Solaiman Ghaus
noch mal dein Makro abgeändert, Frage wozu das "objZ.Select"
siehst du es
Post by Solaiman Ghaus
gerne wenn der Rechnet arbeitet ;-)
[...]
Sorry! Code-Zeile "objZ.Select" stammte aus älteren Proben mit
"Stop" danach, damit ich in der Tat die Arbeit des Rechners
überprüfen konnte! bzw. sinnieren konnte, weswegen das Makro in
einer bestimmten Zelle stehen blieb. Auf jeden Fall gibt es nunmehr
die Code-Zeile von Solaiman: "Application.StatusBar = "Ersetzung in:
" & Suche.Address".

Hast Du eine Lösung für die Angabe der tatsächliche Anzahl von
ersetzten oder gefundenen Worten?

Danke für Dein Feedback im Voraus!
Leslie
Loading...