VBA: String in einer Excel-Zelle suchen und ersetzen: Einschränkungen?
Leslie Anne
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
Leslie Anne schrieb:
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.
> 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!
> 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.
> 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.
> 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
'---------------------------------------------------------------------
> Vielen Dank fürs Feedback im Voraus!!!
Hoffe, damit ist die Frage beantwortet.
> Leslie Anne
Gruß
Solaiman
Wolfgang Habernoll
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())
End If
>Snip
mfG
Wolfgang Habernoll
"Leslie Anne" <greg...@hotmail.com> schrieb im Newsbeitrag
news:%23l2RpGI...@TK2MSFTNGP03.phx.gbl...
Leslie Anne
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
Leslie Anne
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
--------------------------------------------------------------------
"Wolfgang Habernoll" antwortete:
Solaiman Ghaus
Leslie Anne schrieb:
> Hallo Solaiman,
>
> Vielen Dank für Deine zügige Antwort. Ich brauchte Zeit, um Deine
> und Wolfgangs Feedback auszuprobieren.
gern geschehen, kein Problem.
> 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.
Das kann ich nicht nachvollziehen. Ich habe damit Zellen mit über 1500 -
9300 Zeichen durchsucht, hat alles reibungslos geklappt - auch mit
MatchCase True/False.
> 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
> 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.
> 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 ;-)
> 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
'---------------------------------------------------------------------
> Nochmals vielen Dank und
> Schöne Grüße!
> Leslie
Viele Grüße
Solaiman
Leslie Anne
"Solaiman Ghaus" antwortete:
> Hallo Leslie,
>
> Leslie Anne schrieb:
> > Hallo Solaiman,
[...]
> > 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.
>
> 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.
> > 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.
Bezieht sich auch auf mein ursprüngliches Makro.
> 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
> > 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.
[...]
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
Wolfgang Habernoll
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
"Leslie Anne" <greg...@hotmail.com> schrieb im Newsbeitrag
news:O1W4TtTj...@TK2MSFTNGP02.phx.gbl...
Solaiman Ghaus
Leslie Anne schrieb:
> 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!
> Aber Dein modifiziertes Code (unten) hat bei mir funktioniert! Siehe
> bitte meine letzte Frage am Ende.
Ist doch prima, freut mich.
> [...]
>
> 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" ;-)
> Schöne Grüße
> Leslie
Gruß
Solaiman
Leslie Anne
Wolfgang schrieb:
> Hallo Leslie
[...]
> 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.
Das ist okay. Makro sehr nützlich wegen For/Each-Struktur.
[...]
> !! 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.
> 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.
Anzahl der gefundenen Worte wäre besser für meine Zwecke, aber WIE?
> noch mal dein Makro abgeändert, Frage wozu das "objZ.Select"
siehst du es
> 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
Leslie Anne
Solaiman schrieb:
> Hallo Leslie,
[...]
> 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" ;-)
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
Leslie Anne schrieb:
> 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.
> 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.
> Ich danke für Dein Feedback im Voraus!
> Leslie
>
Gruß
Solaiman
Leslie Anne
> Hallo Leslie,
>
> Leslie Anne schrieb:
> > Hallo Solaiman,
> > 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.
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
Leslie Anne schrieb:
> 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 ;-)
> 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.
> 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 ;-)
Leslie Anne
>Solaiman schrieb:
> Hallo Leslie,
[...]
> Es gibt mehrere Möglichkeiten, den Zähler entsprechend der
tatsächlichen
> Ersetzungen hoch zu zählen. Eine davon z. B. folgenden:
'-------------------------------------------------------------------
Funtastisch! Sub Ersetzen_V3() hat bei mir auch funktioniert mit
MatchCase:=True zusammen mit vbBinaryCompare.
> 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
Leslie Anne schrieb:
> Hallo Solaiman,
>
> Funtastisch! Sub Ersetzen_V3() hat bei mir auch funktioniert mit
> MatchCase:=True zusammen mit vbBinaryCompare.
freut mich, schön.
> 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.
> 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.
> 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
'--------------------------------------------------------------------------------------------------
> 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.
> Schöne Grüße
> Leslie
Viele Grüße
Solaiman