Chr160 per VBA ersetzen ändert String ab
30.04.2019 15:39:24
Matthias
Ich habe ein Problem das Ersetzen des Chr160 per VBA betrifft.
Folgendes: Ich exportiere aus einem Programm eine Tabelle. Dies geht jedoch nur mittels einer HTML Datei. Während des Exports werden die Spalten jedoch einfach alle aneinander gereiht und leere Bereiche werden mit dem Chr160 aufgefüllt. Lese ich die Datei nun in Excel ein, dann erhalten ich in der ersten Zelle jeder Zeile einen String, der die gesamte Zeile aus dem Programm enthält. Ich habe es schon geschafft diesen per VBA und Text in Spalten mit festen Bereichen wieder zu trennen.
Nun zu meinem Problem. Als nächstes möchte ich die Chr160 durch normale Leerzeichen ersetzen sowie im selben Schritt in jeder Zelle vor und nachstehende Leerzeichen über Trim entfernen.
Dazu nutze ich folgenden Code, den ich im Netz gefunden und ein bisschen modifiziert habe:
Sub Chr160ersetzen()
Dim r As Range, c As Range
On Error Resume Next
Set r = Range("A1:H220")
On Error GoTo 0
If Not r Is Nothing Then
For Each c In r
c.Replace Chr(160), " ", xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
_
False, ReplaceFormat:=""
c.Value = Trim(LTrim(c.Value))
Next
End If
End Sub
Dieser Code Funktioniert grundsätzlich, aber:
mache Spalten enthalten Zahlen mit Tausendertrennung in dieser Form z.B.
SpalteA
80.700
56
3.650
SpalteB
56
65.000
51.510
SpalteC
561
5.654
355.561
Lasse ich den Code durchlaufen, dann wird daraus:
SpalteA
80,7
56
3,65
SpalteB
56
65
51,51
SpalteC
561
5,654
355,561
Das Tausendertrennzeichen wird also durch ein Komma ersetzt und die Werte somit verändert. Zusätzlich werden Werte im Hunderter, Zehner oder Einerbereich (also alle Werte ohne Trennzeichen) automatisch als Zahl erkannt. Die Veränderten Werte mit dem ersetzten Komma verbleiben jedoch als String. Lasse ich die Trim Funktion (die v.a. für Spalten mit Text gedacht ist) erst mal weg, dann hab ich das selbe Ergebnis.
Noch dazu kommt, dass nicht immer alle Tausendertrennzeichen durch Kommata ersetzt werden. es kommt also auch schon mal zu solch einer Darstellung
SpalteA
80.700
56
3,65
SpalteB
56
65
51.510
SpalteC
561
5,654
355,561
Es ist jedoch unbedingt erforderlich, dass die Zahlen alle in ihrem ursprünglichen Format bleiben.
Wenn ich statt des Codes manuell über Suchen und Ersetzten gehe und so das CHR160 durch ein normales Leerzeichen ersetze und anschließend Formatierung des Strings/Text in ein Zahlenformat ändere (egal ob manuell oder per VBA Code), dann bleiben alle Zahlen wie gewünscht in ihrem Format.
SpalteA
80.700
56
3.650
SpalteB
56
65.000
51.510
SpalteC
561
5.654
355.561
Irgendwo hakt es also am Replace. Ich komme leider nicht weiter. Ich habe auch schon versucht die Autoformatierungen? von Exel auszuschalten. Komme aber zu keinem brauchbaren Ergebniss.
Insgesamt habe ich über 2000 Sheets mit Daten, die so nach bearbeitet werden müssen. Ein manuelles Vorgehen ist also leider keine Option. Habt ihr eine Idee für mich?
Danke schon mal, Matthias