Suchen/Ersetzen mit VBA
HeliKopf
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
> 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
Am Tue, 10 Nov 2009 22:07:27 +0100 schrieb Andreas Killer:
> 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
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
--
http://michael-schwimmer.de
Masterclass Excel VBA ISBN-10: 3827325250
Das Excel-VBA Codebook ISBN-10: 3827324718
Microsoft Office Excel 2007-Programmierung ISBN-10: 3866454139
Andreas Killer
> 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), "��", "=")
HeliKopf
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
> 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
> 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.
> 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 _
HeliKopf
...
> 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
> 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
Oh, hatte ich das garnicht geschrieben? Ich nutze Excel 2003 in einer
Windows Server 2003 Umgebung.
Danke noch mal an Alle Hilfestellungen!
Servus, Helmut.