Suchen/Ersetzen mit VBA

[HeliKopf]

Hallo zusammen,

ich bin gerade am verzweifeln...

Mit einem Makro versuche ich, Daten aus einer Tabelle in eine andere
zu kopieren
(nur Werte) und will anschliessend die Daten mit Suchen/Ersetzen
bearbeiten.
Manuell funktioniert das super, da ersetze ich "§§" mit "=" und schon
werden aus
meinen eingefügten Werten die notwendigen Formeln. Ich habe diesen
Vorgang nur
in verschiedenen Varianten aufgezeichnet, doch leider weigert sich
Excel, diesen
Ersetzen-Vorgang per VBA durchzuführen! Nur wenn nach dem Ersetzen
keine Formel
entsteht, funktioniert das Ganze, aber mit Zielsetzung Formel will nix
laufen.
Hier noch mein Code-Schnipsel dazu:

Selection.Replace What:="§§", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

Vielen Dank schon mal für jegliche Hilfe!

Servus,
Helmut.

[Andreas Killer]

Post by HeliKopf
ich bin gerade am verzweifeln...

Ich auch, ein interessantes Problem das Du das hast, das beschäftigt
mich nun schon ein paar Stunden...., naja meine Frau ist nicht da, in
der Glotze kommt nix... :-)

Also mit VBA geht das nicht weil die Formel eine deutsche Formel ist
und über VBA musst Du eine englische Formel schreiben:

Range("B1") = "=FINDEN(""s"";A1)" 'geht nicht
Range("B1") = "=FIND(""s"",A1)" 'geht

Andersrum geht im Tabellenblatt nur eine deutsche Formel und keine
Englische.

Eine Möglichkeit wäre nun nach §§ zu suchen und dann die Formel ins
englische zu übersetzen.

Noch interessanter ist eine Lösung die ich gefunden habe, aber warum
das geht weiß ich nicht. :-)))

Wenn jemand eine Idee hat, immer her damit.

Interessanter Weise kann man das Blatt ja komplett in ein Array
einlesen, dieses bearbeiten und zurückschreiben. Ist sau schnell.

Habe ich probiert und nanu? Bei dieser Methode kann ich eine deutsche
Formel in ein Blatt schreiben???????????????????????

Der Haken kommt in dem Moment wenn ich das ein 2tes Mal laufen lasse,
dann lese ich über .Formula die englischen Formeln in das Array rein
und beim zurückschreiben gibt es einen Fehler. Übersetze ich die
Formeln ins Deutsche, dann kann ich sie auch zurückschreiben.

Wieso, weshalb, warum ist das so? Wer kann mir das erklären?

Andreas.

Sub Test()
Dim Data, I As Long, J As Long
Data = ActiveSheet.UsedRange.Formula
For J = LBound(Data) To UBound(Data)
For I = LBound(Data, 2) To UBound(Data, 2)
Data(J, I) = Replace(Data(J, I), "§§", "=")
If Left$(Data(J, I), 1) = "=" Then _
Translate Data(J, I)
Next
Next
ActiveSheet.UsedRange.Formula = Data
End Sub

Private Static Sub Translate(Formel)
'Übersetzt englische Formeln ins deutsche
Dim D(0 To 3), E(0 To 3)
Dim I As Integer, J As Integer, Init As Boolean
If Not Init Then
Init = True
D(0) = Array("ABRUNDEN", "ABS", _
"ACHSENABSCHNITT", "ADRESSE", "ANZAHL", _
"ANZAHL2", "ANZAHLLEEREZELLEN", "ARCCOS", _
"ARCCOSHYP", "ARCSIN", "ARCSINHYP", "ARCTAN", _
"ARCTAN2", "ARCTANHYP", "AUFRUFEN", _
"AUFRUNDEN", "BEREICH.VERSCHIEBEN", _
"BEREICHE", "BESTIMMTHEITSMASS", "BETAINV", _
"BETAVERT", "BINOMVERT", "BOGENMASS", "BW", _
"CHIINV", "CHITEST", "CHIVERT", "CODE", _
"COS", "COSHYP", "DATUM", "DATWERT", _
"DBANZAHL", "DBANZAHL2", "DBAUSZUG", "DBMAX", _
"DBMIN", "DBMITTELWERT", "DBPRODUKT", _
"DBSTDABW", "DBSTDABWN", "DBSUMME", _
"DBVARIANZ", "DBVARIANZEN", "DIA", "DM", _
"ERSETZEN", "EXP", "EXPONVERT", "FAKULTÄT", _
"FALSCH", "FEHLER.TYP", "FEST", "FINDEN", _
"FINV", "FISHER", "FISHERINV")
E(0) = Array("ROUNDDOWN", "ABS", "INTERCEPT", _
"ADDRESS", "COUNT", "COUNTA", "COUNTBLANK", _
"ACOS", "ACOSH", "ASIN", "ASINH", "ATAN", _
"ATAN2", "ATANH", "CALL", "ROUNDUP", _
"OFFSET", "AREAS", "RSQ", "BETAINV", _
"BETADIST", "BINOMDIST", "RADIANS", "PV", _
"CHIINV", "CHITEST", "CHIDIST", "CODE", _
"COS", "COSH", "DATE", "DATEVALUE", "DCOUNT", _
"DCOUNTA", "DGET", "DMAX", "DMIN", _
"DAVERAGE", "DPRODUCT", "DSTDEV", "DSTDEVP", _
"DSUM", "DVAR", "DVARP", "SYD", "DOLLAR", _
"REPLACE", "EXP", "EXPONDIST", "FACT", _
"FALSE", "ERROR.TYPE", "FIXED", "FIND", _
"FINV", "FISHER", "FISHERINV")
D(1) = Array("FTEST", "FVERT", "GAMMAINV", _
"GAMMALN", "GAMMAVERT", "GANZZAHL", "GDA", _
"GDA2", "GEOMITTEL", "GERADE", _
"GESTUTZTMITTEL", "GLÄTTEN", "GRAD", "GROSS", _
"GROSS2", "GTEST", "HARMITTEL", "HÄUFIGKEIT", _
"HEUTE", "HYPGEOMVERT", "IDENTISCH", "IKV", _
"INDEX", "INDIREKT", "INFO", "ISTBEZUG", _
"ISTFEHL", "ISTFEHLER", "ISTKTEXT", _
"ISTLEER", "ISTLOG", "ISTNV", "ISTTEXT", _
"ISTZAHL", "JAHR", "JETZT", "KAPZ", _
"KGRÖSSTE", "KKLEINSTE", "KLEIN", _
"KOMBINATIONEN", "KONFIDENZ", "KORREL", _
"KOVAR", "KRITBINOM", "KURT", "KÜRZEN", _
"LÄNGE", "LIA", "LINKS", "LN", "LOG", _
"LOG10", "LOGINV", "LOGNORMVERT", "MAX", _
"MDET")
E(1) = Array("FTEST", "FDIST", "GAMMAINV", _
"GAMMALN", "GAMMADIST", "INT", "DDB", "DB", _
"GEOMEAN", "EVEN", "TRIMMEAN", "TRIM", _
"DEGREES", "UPPER", "PROPER", "ZTEST", _
"HARMEAN", "FREQUENCY", "TODAY", _
"HYPGEOMDIST", "EXACT", "IRR", "INDEX", _
"INDIRECT", "INFO", "ISREF", "ISERR", _
"ISERROR", "ISNONTEXT", "ISBLANK", _
"ISLOGICAL", "ISNA", "ISTEXT", "ISNUMBER", _
"YEAR", "NOW", "PPMT", "LARGE", "SMALL", _
"LOWER", "COMBIN", "CONFIDENCE", "CORREL", _
"COVAR", "CRITBINOM", "KURT", "TRUNC", "LEN", _
"SLN", "LEFT", "LN", "LOG", "LOG10", _
"LOGINV", "LOGNORMDIST", "MAX", "MDETERM")
D(2) = Array("MEDIAN", "MIN", "MINUTE", "MINV", _
"MITTELABW", "MITTELWERT", "MMULT", _
"MODALWERT", "MONAT", "MTRANS", "N", "NBW", _
"NEGBINOMVERT", "NICHT", "NORMINV", _
"NORMVERT", "NV", "OBERGRENZE", "ODER", _
"PEARSON", "PI", "POISSON", "POTENZ", _
"PRODUKT", "QIKV", "QUADRATESUMME", _
"QUANTIL", "QUANTILSRANG", "QUARTILE", _
"RANG", "RECHTS", "REGISTER.KENNUMMER", _
"REST", "RGP", "RKP", "RMZ", "RÖMISCH", _
"RUNDEN", "SÄUBERN", "SCHÄTZER", "SCHIEFE", _
"SEKUNDE", "SIN", "SINHYP", "SPALTE", _
"SPALTEN", "STABW", "STABWN", _
"STANDARDISIERUNG", "STANDNORMINV", _
"STANDNORMVERT", "STEIGUNG", "STFEHLERYX", _
"STUNDE", "SUCHEN", "SUMME", "SUMMENPRODUKT")
E(2) = Array("MEDIAN", "MIN", "MINUTE", _
"MINVERSE", "AVEDEV", "AVERAGE", "MMULT", _
"MODE", "MONTH", "TRANSPOSE", "N", "NPV", _
"NEGBINOMDIST", "NOT", "NORMINV", "NORMDIST", _
"NA", "CEILING", "OR", "PEARSON", "PI", _
"POISSON", "POWER", "PRODUCT", "MIRR", _
"SUMSQ", "PERCENTILE", "PERCENTRANK", _
"QUARTILE", "RANK", "RIGHT", "REGISTER.ID", _
"MOD", "LINEST", "LOGEST", "PMT", "ROMAN", _
"ROUND", "CLEAN", "FORECAST", "SKEW", _
"SECOND", "SIN", "SINH", "COLUMN", "COLUMNS", _
"STDEV", "STDEVP", "STANDARDIZE", "NORMSINV", _
"NORMSDIST", "SLOPE", "STEYX", "HOUR", _
"SEARCH", "SUM", "SUMPRODUCT")
D(3) = Array("SUMMEWENN", "SUMMEX2MY2", _
"SUMMEX2PY2", "SUMMEXMY2", "SUMQUADABW", _
"SVERWEIS", "T", "TAG", "TAGE360", "TAN", _
"TANHYP", "TEIL", "TEILERGEBNIS", "TEXT", _
"TINV", "TREND", "TTEST", "TVERT", "TYP", _
"UND", "UNGERADE", "UNTERGRENZE", "VARIANZ", _
"VARIANZEN", "VARIATION", "VARIATIONEN", _
"VDB", "VERGLEICH", "VERKETTEN", "VERWEIS", _
"VORZEICHEN", "WAHL", "WAHR", "WAHR", _
"WAHRSCHBEREICH", "WECHSELN", "WEIBULL", _
"WENN", "WERT", "WIEDERHOLEN", "WOCHENTAG", _
"WURZEL", "WVERWEIS", "ZÄHLENWENN", _
"ZEICHEN", "ZEILE", "ZEILEN", "ZEIT", _
"ZEITWERT", "ZELLE", "ZINS", "ZINSZ", _
"ZUFALLSZAHL", "ZW", "ZZR")
E(3) = Array("SUMIF", "SUMX2MY2", "SUMX2PY2", _
"SUMXMY2", "DEVSQ", "VLOOKUP", "T", "DAY", _
"DAYS360", "TAN", "TANH", "MID", "SUBTOTAL", _
"TEXT", "TINV", "TREND", "TTEST", "TDIST", _
"TYPE", "AND", "ODD", "FLOOR", "VAR", "VARP", _
"GROWTH", "PERMUT", "VDB", "MATCH", _
"CONCATENATE", "LOOKUP", "SIGN", "CHOOSE", _
"TRUE", "TRUE", "PROB", "SUBSTITUTE", _
"WEIBULL", "IF", "VALUE", "REPT", "WEEKDAY", _
"SQRT", "HLOOKUP", "COUNTIF", "CHAR", "ROW", _
"ROWS", "TIME", "TIMEVALUE", "CELL", "RATE", _
"IPMT", "RAND", "FV", "NPER")
End If

For I = LBound(D) To UBound(D)
For J = LBound(D(I)) To UBound(D(I))
Formel = Replace(Formel, E(I)(J) & "(", D(I)(J) & "(")
Next
Next
Formel = Replace(Formel, ",", ";")
End Sub

[Michael Schwimmer]

Hallo Andreas,

Post by Andreas Killer
Also mit VBA geht das nicht weil die Formel eine deutsche Formel ist
Range("B1") = "=FINDEN(""s"";A1)" 'geht nicht
Range("B1") = "=FIND(""s"",A1)" 'geht

das ist der Fluch der Standardeigenschaft, die zur Bequemlichkeit
verleitet.

Benutze entweder Formula oder FormulaLoca, anstatt die Standardeigenschaft
(Value) zu verwenden.

Me.Range("D1").FormulaLocal = "=FINDEN(""s"";A1)" 'geht

Me.Range("E1").Formula = "=FIND(""s"",A1)" 'geht
Me.Range("E1").Value = "=FIND(""s"",A1)" 'geht


Besser wäre natürlich, wenn man generell gezwungen würde, vollständig zu
referenzieren und die gewünschte Eigenschaft anzugeben!

Also beispielsweise statt
MsgBox Range("E1")
folgendes
MsgBox Worksheets(1).Range("E1").Value

Viele Grüße
Michael

[Andreas Killer]

Post by Michael Schwimmer
Benutze entweder Formula oder FormulaLoca, anstatt die Standardeigenschaft
(Value) zu verwenden.

Ja aber sicher! Hey Danke, jetzt ist der Groschen gefallen, nun läßt
sich das Replace-Problem des OP auch ohne Schnick-Schnack-Übersetzung
lösen. Vielen Dank.

Aber warum das mit dem Array so geht, kannst Du dazu auch was sagen?

Andreas.

Sub Test()
Dim Data, I As Long, J As Long
Data = ActiveSheet.UsedRange.FormulaLocal
For J = LBound(Data) To UBound(Data)
For I = LBound(Data, 2) To UBound(Data, 2)
Data(J, I) = Replace(Data(J, I), "§§", "=")
Next
Next
ActiveSheet.UsedRange.Formula = Data
End Sub

[HeliKopf]

Guten Morgen zusammen,

wow super, vielen Dank für die Hilfe, jetzt funktioniert das echt gut
- zumindest was das ersetzen der Werte angeht. Leider werden die dann
eingefügten Daten aber nicht als Formel erkannt und der Text bleibt
einfach stehen ohne dass eine Berechnung ausgeführt wird.
AZudem habe ich noch ein wenig Bammel, wie sich das bei grossen
Datenmengen verhält... Aktuell haben meine User noch recht kleine
Listen, die verarbeitet werden, aus der Vergangenheit weis ich aber,
dass diese Tabellen dann bis zu 50.000 Zeilen und 80 Spalten bekommen.
Könnte das ein Performance-Problem geben?
Ah und im Code war noch was verdreht: letzte Zeile muss dann ebenfalls
mit FormulaLocal sein :-)

Vielen Dank schon mal für die Lösungen!

Servus,
Helmut.

[HeliKopf]

Post by HeliKopf
Guten Morgen zusammen,
... Leider werden die dann
eingefügten Daten aber nicht als Formel erkannt und der Text bleibt
einfach stehen ohne dass eine Berechnung ausgeführt wird.

... jetzt hab' ich mich selbst überlistet: Zellen waren als "Text"
formatiert, mit Standard-Formatierung funktionierts. Ich brauch' wohl
noch nen Kaffee :)

Danke & Sevus,
Helmut.

[Andreas Killer]

Post by HeliKopf
AZudem habe ich noch ein wenig Bammel, wie sich das bei grossen
Datenmengen verhält... Aktuell haben meine User noch recht kleine
Listen, die verarbeitet werden, aus der Vergangenheit weis ich aber,
dass diese Tabellen dann bis zu 50.000 Zeilen und 80 Spalten bekommen.
Könnte das ein Performance-Problem geben?

Nö, eher andersrum, die Verarbeitung im Array ist die schnellste
Möglichkeit... bei vielen Formeln.

Fragt sich natürlich wieviele umzuwandelnde Formeln Du hast, sind es
nur ein paar wenige, dann wird wohl

Sub Test2()
Dim C As Range
Set C = Cells.Find("§§", LookIn:=xlValues, LookAt:=xlPart)
Do While Not C Is Nothing
C.FormulaLocal = Replace(C, "§§", "=")
Set C = Cells.FindNext(C)
Loop
End Sub

schneller sein.

Letztens hatten wir hier ein Thema bei dem es um das löschen von
Zeilen geht wenn in dieser ein Wert < 0 steht. Bei einer Datenmenge
von 20 Spalten und 50.000 Zeilen braucht das Array ca. 1 Sekunde.

Musst Du selber probieren.

Post by HeliKopf
Ah und im Code war noch was verdreht: letzte Zeile muss dann ebenfalls
mit FormulaLocal sein :-)

Nö, das spielt offenbar keine Rolle ob man hier .Formula,
.FormulaLocal oder .Value sagt, geht alles. Nur beim Einlesen muss man
.FormulaLocal nehmen.

.Value solltest Du aber vorsichtshalber bei der Zuweisung nicht nehmen.

BTW, Du solltest aber vor dem generellen Replace prüfen ob da was drin
steht, sonst verwandeln sich leere Zellen in Zellen mit "".

Außerdem kannst Du auch prüfen ob §§ am Anfang steht.

Andreas.

Sub Test()
Dim Data, I As Long, J As Long
Data = ActiveSheet.UsedRange.FormulaLocal
For J = LBound(Data) To UBound(Data)
For I = LBound(Data, 2) To UBound(Data, 2)
If Not IsEmpty(Data(J, I)) Then _
If Left$(Data(J, I), 2) = "§§" Then _
Data(J, I) = Replace(Data(J, I), "§§", "=")
Next
Next
ActiveSheet.UsedRange.Formula = Data
End Sub

[HeliKopf]

On 11 Nov., 10:26, Andreas Killer <***@gmx.net> wrote:
...

Post by Andreas Killer
Sub Test()
   Dim Data, I As Long, J As Long
   Data = ActiveSheet.UsedRange.FormulaLocal
   For J = LBound(Data) To UBound(Data)
     For I = LBound(Data, 2) To UBound(Data, 2)
       If Not IsEmpty(Data(J, I)) Then _
         If Left$(Data(J, I), 2) = "§§" Then _
           Data(J, I) = Replace(Data(J, I), "§§", "=")
     Next
   Next
   ActiveSheet.UsedRange.Formula = Data
End Sub

Danke für die Abermals sehr hilfreichen Infos, mit dem Array gehts
auch bei meiner grössten Datei in Sekunden, also perfekt! Allerdings
bekomme ich eine Fehlermeldung, wenn ich statt FormulaLocal in der
letzten Code-Zeile Formula benutze: Laufzeitfehler '1004': Anwendungs-
oder objektdefinierter Fehler.
Keine Ahnung, woran das bei mir wieder liegt...

Servus, Helmut.

[Andreas Killer]

Post by HeliKopf
bekomme ich eine Fehlermeldung, wenn ich statt FormulaLocal in der
letzten Code-Zeile Formula benutze: Laufzeitfehler '1004': Anwendungs-
oder objektdefinierter Fehler.
Keine Ahnung, woran das bei mir wieder liegt...

Aha, gut zu wissen, liegt wahrscheinlich an der Excel-Version. Ich hab
Xl2002, was hast Du?

Andreas.

[HeliKopf]

Post by Andreas Killer

Post by HeliKopf
bekomme ich eine Fehlermeldung, wenn ich statt FormulaLocal in der
letzten Code-Zeile Formula benutze: Laufzeitfehler '1004': Anwendungs-
oder objektdefinierter Fehler.
Keine Ahnung, woran das bei mir wieder liegt...

Aha, gut zu wissen, liegt wahrscheinlich an der Excel-Version. Ich hab
Xl2002, was hast Du?
Andreas.

Oh, hatte ich das garnicht geschrieben? Ich nutze Excel 2003 in einer
Windows Server 2003 Umgebung.

Danke noch mal an Alle Hilfestellungen!

Servus, Helmut.