# EXCEL Makro. 3 Dezimalstellen bei Kommazahlen, andere ignorieren



## mr_ds (13. Januar 2011)

Hi Leute,

Bin schon seit 2 Tagen auf der Suche nach einer Lösung für mein Problem, leider konnte mir das liebe Internet nicht weiterhelfen, weil die Frage vermutlich zu speziell ist.

Mein Problem: ich exportiere Daten aus SiteCatalyst in Excel. 
Die Daten werden beispielsweie wie folgt angezeigt:

Die Tausender werden mit Kommastellen dargestellt z.B.  16,625
Die Hunderter ganz normal: z.B. 875

Problem ist nun folgendes: wenn eine Tausenderzahl eine, zwei oder drei Nullen hinten hat fallen diese einfach weg.

3,500 wird dann als 3,5 angezeigt.  
4,580 als 4,58  usw.

Wie kann ich das Problem nun lösen damit ich ausschließlich nur bei den Kommazahlen 3 Dezimalstellen angezeigt bekomme?
Am liebsten hätte ich anstatt den Kommazahlen noch Punkte 

Ich werde zukünftig auch mönatlich den Report erstellen müssen, wäre also super wenn ich da einfach immer nur ein Makro drüber laufen lassen könnte (über mehrere Reiter).

Ich hoffe ich habe mich verstädlich ausgedrückt und bedanke mich schon im Voraus bei den Profis 

LG
DS


----------



## tombe (13. Januar 2011)

So spontan fällt mir da nix richtiges ein. Aber hilft es velleicht hierfür vorübergehend das Zahlenformat auf das englische Format umzustellen?

Dort werden die Zahlen ja mit Komma als Tausendertrennzeichen formatiert.


----------



## mr_ds (13. Januar 2011)

Vielen Dank für die schnelle Antwort. 
Jetzt weiß ich zumindest mal dass man die Fragestellung verstanden hat. 

Das hilft leider nichts, das habe ich schon ausprobiert. Die Nullen erscheinen dadurch leider auch nicht wieder.


----------



## Drogist (13. Januar 2011)

Moin mr_ds,

ich nehme einmal an, dass du nicht nur in das *.xls-Format exportieren kannst sondern auch nach *.csv *.txt oder ähnlich. Diese Datei kannst du dann importieren und beim Import dann auch die Dezimaltrennzeichen festlegen.

... hättest du die Excel-Version genannt, stände oben der komplette Weg.


----------



## mr_ds (17. Januar 2011)

Moin Moin,

ich importiere die Daten mit einem Excel-Add-in (die Werte werden in unformatierte Zellen geschrieben), kann also nicht direkt in andere Formate exportieren, leider.
Ich verwende Excel 2010. 

Viele Grüße und einen guten Start in die neue Woche


----------



## Drogist (17. Januar 2011)

mr_ds hat gesagt.:


> ich importiere die Daten mit einem Excel-Add-in


Und genau da liegt das Problem. Vielleicht, aber nur vielleicht klappt es, wenn du das System bei den Ländereinstellungen aus US umstellst.

Ich habe mal kurz gegoogelt, wenn es das Analyse-Tool für Websites ist, dann geht auch ein Export nach Word. Das würde ich als Zwischenschritt nutzen und die Werte in Word entsprechend anpasen. Dann per Copy&Paste nach Excel.


----------



## tombe (18. Januar 2011)

Komisch, ich war mir sicher das wenn man in den Ländereinstellungen das Zahlenformat auf englisch umstellt, das es dann geht. Doch so geht es tatsächlich nicht.

Wenn man allerdings das Zahlenformat auf deutsch stehen lässt und dann die Einstellungen manuell anpasst, dann klappt es.

Systemsteuerung -> Regions- und Sprachoptionen -> Regionale Einstellungen -> Anpassen und hier dann beim Dezimaltrennzeichen das Komma in einen Punkt ändern und beim Symbol für Zifferngruppierung aus dem Punkt ein Komma machen.

Dann noch alles mit Ok bestätigen und ganz wichtig, nach den Änderungen Excel neu starten.


----------



## mr_ds (18. Januar 2011)

Vielen Dank für eure Antworten, Drogist und tombe,

ganz genau, es ist ein Analyse-Tool für Websites. Man erstellt Reports und diese kann man dann einzeln als PDF, Word, CSV oder Excel runterladen. Mehr als 1 Report können nur als PDF runtergeladen werden. 

Das Sinn von dem Add-in ist komplexe Verflechtungen zwischen vielen Reports erstellen zu können, und quasi "freier" Statistiken, Diagramme, Auswertungen, usw. machen zu können.
Man selektiert die Daten die man dann aus dem System in Excel zieht und kann dann mit den Zahlen in Excel arbeiten. 

Das ganze soll eine Automatisierung sein, die jeden Monat "refreshed" werden kann. Habe also gehofft dass man ein Makro schreiben kann, das die Werte aufbereitet. 

Der Umweg über Word ist ein guter Ansatz, wäre aber zu zeititensiv und ein Widerspruch der Automatisierung.

Ich teste jetzt mal die Umstellung der Ländereinstellungen.

I'll keep you posted


----------



## mr_ds (18. Januar 2011)

Voller Hoffnung habe ich die Ländereinstellungen vorgenommen, Excel neu gestertet, Abfrage neu gestartet, leider ohne Erfolg. 3500 wird immer noch als 3,5 angezeigt.

Langsam weiß ich keinen Rat mehr


----------



## tombe (18. Januar 2011)

Kannst du mal eine solche Datei oder zumindest einen Teil davon hier einstellen damit man mal mit Originaldaten testen kann.

Habe bei mir vorhin eine Datei mit folgendem Inhalt erstellt


```
1,200; enthält die Zahl 1200
1,111.11; enthält die Zahl 1111,11
2,000; enthält die Zahl 2000
555.55; enthält die Zahl 555,55
```

als CSV-Datei gespeichert und wie oben beschrieben in Excel eingelesen. Bei mir hat es so wunderbar geklappt. Es konnten auch weitere Zahlen eingegeben werden und sie wurden richtig formatiert.


----------



## mr_ds (18. Januar 2011)

Meinst Du so was in der Art, oder wirklich ein komplette Datei?
Die unteren Zahlen sind Originalzahlen, so wie sie in Excel nach dem Import, eingesetzt werden. 
3,5 sollten in diesem Beispiel 3500 sein.

A	        2,497	                 3,5
B	        202	                         309
C	        156	                         259
D	        148	                         250
E	        103	                          175
F	         98	                         170
G	         109	                         165
H	          87	                         142
I	          86	                         127


----------



## mr_ds (18. Januar 2011)

Wenn ich die Datei als CSV speichern will kommt die Meldung: "Der ausgewählte Dateityp unterstützt keine Arbeitsmappen, die mehrere Blätter enthalten"


----------



## tombe (18. Januar 2011)

"CSV" habe ich nur ins Spiel gebracht weil ich dachte deine Daten wären im Original so gespeichert.

Wenn dir eine Tabelle reicht, kannst du die Meldung einfach ignorieren, es werden einfach die Tabellenblätter 2, 3, ... geöscht.

Ist das da oben das was Excel nach dem Importieren daraus macht oder sind das die Daten wie sie von SiteCatalyst kommen?


----------



## mr_ds (18. Januar 2011)

In SiteCatalyst werden die Daten mit Komma dargestellt. z.B. 2,745  für Zweitausend...

Die Daten oben sind so, wie sie importiert werden.

Die CSV-Lösungen muss ich jetzt jedenfalls schwer in Erwägung ziehen, weil ich sonst anscheinend immer manuell die Kommazahlen korrigieren muss.

Porblem ist nur dass diese Lösung wieder mauellen Aufwand bedeutet und ich mindestens zwei Tage verlieren würde um alles neu zu verlinken mit Diagrammen S-Verweisen usw.

An der Stelle aber schon mal ein dickes Dankeschön für eure Mühe!


----------



## tombe (18. Januar 2011)

Hast du dich dann oben verschrieben oder warum wird bereits beim Export die Zahl 3500 nur als 3,5 und nicht als 3,500 dargestellt?

Denn nur in dem Fall das bei einer Tausenderzahl keine Nullen dran sind habe ich hier ein Problem, anderfalls klappt es bei mir wie oben geschrieben.


----------



## mr_ds (18. Januar 2011)

Ich wünschte ich hätte mich verschrieben 

Leider ist das tatsächlich so, dass 3,5 (ohne Nullen) in die Zelle geschrieben wird.


----------



## tombe (18. Januar 2011)

Schau mal ob du damit etwas anfangen kannst:


```
Sub zahl_format()
'Bildschirmanzeige ausschalten
Application.ScreenUpdating = False

'letzte Zeile in Spalte A suchen
zeilen% = Tabelle1.Cells(Rows.Count, 1).End(xlUp).Row

'letzte Spalte in Zeile 1 suchen
spalten% = Tabelle1.Cells(1, Columns.Count).End(xlToLeft).Column

'alle Spalten durchlaufen
For s% = 1 To spalten%

'alle Zeilen durchlaufen
    For z% = 1 To zeilen%
'wenn Komma in der Zelle vorkommt
        If InStr(1, Tabelle1.Cells(z%, s%), ",") Then
'Teil links inklusive Komma
        links$ = Left(Tabelle1.Cells(z%, s%), InStr(1, Tabelle1.Cells(z%, s%), ","))
'Teil rechts vom Komma
        rechts$ = Mid(Tabelle1.Cells(z%, s%), InStr(1, Tabelle1.Cells(z%, s%), ",") + 1, Len(Tabelle1.Cells(z%, s%)))
'Wert formatieren und in Zelle eintragen
        Tabelle1.Cells(z%, s%) = links$ & rechts$ & String(3 - Len(rechts$), "0")
        End If
    Next z%

Next s%

'Bildschirmanzeige wieder einschalten
Application.ScreenUpdating = True
End Sub
```

Da ich den genauen Ablauf nicht kenne wie du die Daten in Excel einliest, kann ich dir jetzt nicht sagen wo du das Makro am besten einbindest. Die Umwandlung klappt soweit ich es jetzt getestet habe so aber.


----------



## mr_ds (18. Januar 2011)

Wow, das ist ein toller Ansatz. Leider tut sich nichts bei mir wenn ich das Makro ausführe.

Mache ich das was falsch? Habe unter  Ansicht das Makro angelegt und wollte es testweise auf einem bestimmten Reiter (NL - Holland) anwenden. 

(Meine Mappe hat 15 Reiter mit verschiedenen Ländern. Ganz vorne habe ich eine Tabelle gebastelt die aus diesen ganzen Reitern, meist per S-Verweis, die gewünschten Informationen zieht.)

Die Daten aus SiteCatalyst werden in jeden Reiter im Bereich A1 bis W57 gezogen. 

Vielleicht helfen die Informationen ja weiter!?

Finde ich nebenbei bemerkt super dass Leuten wie mir, die keine Ahnung von Makros haben, hier so toll geholfen wird!


----------



## tombe (18. Januar 2011)

VORSICHT: Dinge die per Makro geändert wurden lassen sich nicht mit "Bearbeiten Rückgängig" zurücksetzten!!

Ich habe es in meinem Beispiel alles auf "Tabelle1" geschrieben, dort werden also die Änderungen gemacht.

Um dir weiter zu helfen hätte ich jetzt erst 2 Fragen:
1) wie "sensibel" sind die Daten den mit denen du da arbeitest?
2) kannst du mir die Datei schicken und kannst du sie dann als 2000-er Version speichern?

Ansonsten ändere alles was jetzt "Tabelle1" heißt in "ActiveSheet" und (sofern nicht eh schon geschehen) kopiere den Code in das Register "DieseArbeitsmappe", den er darf nicht innerhalb eines bestimmten Arbeitsblattes stehen.

Dann kannst du diesem Makro eine Tastenkombination zuweisen und es immer wenn du in einer bestimmten Tabelle stehst darüber starten.

Beim ermitteln der letzten Zeile/Spalte endet das Makro immer wenn es auf eine leere Zelle trifft! Es du mit dieser Abbruchbedingung klar kommst musst du testen.


----------



## mr_ds (18. Januar 2011)

Habe den Code auf "AvtiveSheet" geändert, da wo Tabelle1 stand. Leider ohne Veränderung. Es passiert nichts.
Zu Deinen Fragen: es sind sensible Daten, aber ich habe eine Beispielsdatei erstellt und als 2000er Version gespeichert. Wie soll ich Dir diese zukommen lassen? Über das Forum kann ich ja keine Dateien verschicken.


----------



## mr_ds (18. Januar 2011)

Ich habe die Datei nochmal auf- und zugemacht..... es hat funktioniert******!

Tombe... du hast es drauf******!!  

Ich Danke dir 1000-fach für Deine tolle unterstützung****** Finde ich super dass sich Leute wie Du anderen zur Verfügung stellen die Hilfe brauchen!

Nochmals vielen Dank..... ich empfehle Dich und diese Seite auf jeden Fall weiter!


----------



## tombe (18. Januar 2011)

Freut mich wenn es geklappt hat.

Ansonsten hätte ich dir meine eMail Adresse per PN geschickt, dann hätten wir es so gemacht.


----------



## mr_ds (18. Januar 2011)

Bevor ich das Thema nun schließe ist mir gerade aufgefallen dass es die Werte wunderbar ändert bis eine leere Spalte kommt. 

Kannst Du mir bitte noch ganz kurz einen Tipp geben wie ich es schaffe das ganze Sheet abzuändern und nicht nur Spalte B und C (A ist Text)?
Spalte D ist leer und dann kommt wieder E mit Text F & G mit Zahlen, dann wieder leer usw.
Datenbereich insgesamt  A1 bis W57 .


----------



## tombe (18. Januar 2011)

Dazu musst du die zwei Zeilen in denen die letzte Zeile bzw. die letzte Spalte ermittelt wird so ändern:


```
'letzte Zeile in Spalte A suchen
zeilen% = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
 
'letzte Spalte in Zeile 1 suchen
spalten% = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
```

Jetzt ist es egal ob es irgendwo dazwischen leere Zellen gibt.


----------



## Drogist (18. Januar 2011)

Moin mr_ds,

schau mal in dein Postfach ...


----------



## mr_ds (19. Januar 2011)

Schönen guten Morgen Tobe,

Vielen Dank für die Antwort, funktioniert mal wieder super!

Folgende Frage ist nun wirklich die letzte mit der ich Dich zu diesem Thema belästige: 

Gibt es die Möglichkeit das Makro über mehrere Reiter/Register laufen zu lassen damit ich nicht alle 15 Stück anklicken und das Makro ausführen muss?

@Drogist: Danke für Deine PN! Habe drauf genatwortet.

Beste Grüße aus Baden-Württemberg


----------



## tombe (19. Januar 2011)

Sorry bin ein bisschen spät dran heute, dafür habe ich eine Lösung für dich.

Neu ist hier nur das die Anzahl der vorhandenen Tabellen ermittelt werden und eine weitere For/Next-Schleife die dann eben nacheinander alle Tabellen durchläuft.


```
Sub zahl_format()
'Bildschirmanzeige ausschalten
Application.ScreenUpdating = False
 
'zuerst die Anzahl der Tabellen ermitteln ** NEUE ZEILE **
tabellen% = ActiveWorkbook.Sheets.Count

'alle Tabellen durchlaufen ** NEUE FOR/NEXT-SCHLEIFE **
For t% = 1 To tabellen%
    ActiveWorkbook.Sheets(t%).Activate
    'letzte Zeile in Spalte A suchen
    zeilen% = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
 
    'letzte Spalte in Zeile 1 suchen
    spalten% = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

    'alle Spalten durchlaufen
    For s% = 1 To spalten%
 
        'alle Zeilen durchlaufen
        For z% = 1 To zeilen%
        'wenn Komma in der Zelle vorkommt
            If InStr(1, ActiveSheet.Cells(z%, s%), ",") Then
                'Teil links inklusive Komma
                links$ = Left(ActiveSheet.Cells(z%, s%), InStr(1, ActiveSheet.Cells(z%, s%), ","))
                'Teil rechts vom Komma
                rechts$ = Mid(ActiveSheet.Cells(z%, s%), InStr(1, ActiveSheet.Cells(z%, s%), ",") + 1, Len(ActiveSheet.Cells(z%, s%)))
                'Wert formatieren und in Zelle eintragen
                ActiveSheet.Cells(z%, s%) = links$ & rechts$ & String(3 - Len(rechts$), "0")
            End If
        Next z%
 
    Next s%
 
Next t%
'Bildschirmanzeige wieder einschalten
Application.ScreenUpdating = True
End Sub
```

Es grüßt ebenfalls aus BaWü

Thomas


----------



## mr_ds (19. Januar 2011)

Vielen Dank schonmal, Thomas.

Leider kommt eine Fehlermeldung, mit dem Verweis auf: 

ActiveSheet.Cells(Z%, s%) = Links$ & rechts$ & String(3 - Len(rechts$), "0")

"Laufzeitfehler '5': Ungültiger Prozeduraufruf oder ungültiges Argument"

Kann es sein dass es wegen dem ersten Tabellenblatt ist? Da hab ich insgesamt 81 Seiten mit Diagrammen, Tabellen und Bildern (Daten kommen aus dem zweiten Tabellenblatt 'Übersicht'. Die Daten für die Übersicht (Tabellenblatt 2) werden aus den weiteren 15 Länder-Tabellenblättern gezogen, die wiederrum aus SiteCatalyst kommen. 

Im Grund genommen könnten die ersten beiden Tabellenblätter ignoriert werden.

Schöne Grüße
Daniel


----------



## tombe (19. Januar 2011)

Der Fehler wird wohl immer dann ausgelöst wenn eine Zahl mehr als 3 Stellen nach dem Komma hat, was ja eigentlich nicht sein dürfte.

Für diesen Fall (oder wenn sonst ein Fehler auftritt) habe ich den Code nochmals geändert. Tritt ein Fehler auf, wird am Ende eine Meldung angezeigt und der Name der Tabelle sowie die Zeile und die Spalte ausgegeben.

Die Schleife ist jetzt auch gleich so geändert das erst in Tabelle 3 begonnen wird.


```
Sub zahl_format()
Dim blatt()
Max% = 0
fehler% = 0

On Error GoTo format_fehler
'Bildschirmanzeige ausschalten
Application.ScreenUpdating = False
 
'zuerst die Anzahl der Tabellen ermitteln ** NEUE ZEILE **
tabellen% = ActiveWorkbook.Sheets.Count
 
'alle Tabellen durchlaufen ** NEUE FOR/NEXT-SCHLEIFE **
For t% = 3 To tabellen%
    ActiveWorkbook.Sheets(t%).Activate
    'letzte Zeile in Spalte A suchen
    zeilen% = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
 
    'letzte Spalte in Zeile 1 suchen
    spalten% = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
 
    'alle Spalten durchlaufen
    For s% = 1 To spalten%
 
        'alle Zeilen durchlaufen
        For z% = 1 To zeilen%
        'wenn Komma in der Zelle vorkommt
            If InStr(1, ActiveSheet.Cells(z%, s%), ",") Then
                'Teil links inklusive Komma
                links$ = Left(ActiveSheet.Cells(z%, s%), InStr(1, ActiveSheet.Cells(z%, s%), ","))
                'Teil rechts vom Komma
                rechts$ = Mid(ActiveSheet.Cells(z%, s%), InStr(1, ActiveSheet.Cells(z%, s%), ",") + 1, Len(ActiveSheet.Cells(z%, s%)))
                'Wert formatieren und in Zelle eintragen
                ActiveSheet.Cells(z%, s%) = links$ & rechts$ & String(3 - Len(rechts$), "0")
            End If
        Next z%
 
    Next s%
 
Next t%
'Bildschirmanzeige wieder einschalten
Application.ScreenUpdating = True

If fehler% > 0 Then
    fehlertext$ = "Es traten insgesamt " & fehler% & " Fehler auf." & Chr$(13) & Chr$(13)
    For a% = 0 To UBound(blatt)
        fehlertext$ = fehlertext$ & blatt(a%) & Chr$(13)
    Next a%
    MsgBox fehlertext$
End If

Exit Sub
format_fehler:
fehler% = fehler% + 1
ReDim Preserve blatt(Max%)
blatt(Max%) = ActiveSheet.Name & " (Zeile " & z% & " - Spalte " & s% & ")"
Max% = Max% + 1
Resume Next
End Sub
```


----------



## mr_ds (19. Januar 2011)

Thomas, ich kann Dir gar nicht genug danken! Respekt, wirklich.

Funktioniert tadellos!


----------

