Tausender Separator mit Makro verändern

[SimonM]

Hallo zusammen,

ich habe ein mittlerweile sehr umfangreiches Makro geschrieben, welches Dateien, welche später in ein Datenbanksystem geladen werden, auf Fehler überprüft.
Darunter zählen zum Beispiel ob Zeilenumbrüche vorhanden oder die Maximallängen der Strings überschritten sind.

Jedoch habe ich noch ein Problem:

Das Datenbankssystem akzeptiert nur Nummern, welche den Tausenderseparator als Komma haben. (Das amerikanische System)

Beispiel:

zulässig: 123,456.78 oder 123,456 oder 0.123 oder 123,456,789
nicht zulässig: 123.456,78 oder 0,123 oder 123.456.789

Sollte ein Wert keine Nachkommastellen besitzen, so sollen auch keine angefügt werden. Sprich 2018 bleibt 2018.
Ich habe bereits mehrere Foren durchsucht und habe das NumberFormat "#,##0.##" gefunden, jedoch kann ich dieses nicht so recht anwenden.

Code:

'The CheckNumeric() method checks if the values are numeric or not. the numeric attribute is defined in the row below the length attributes

'@PARAM:
'ROW As Integer                             -> First Value to identify which cell to check
'COL As Integer                             -> Second Value to identify which cell to check
'SETTINGS_IndexOfFillSheet As Integer       -> The index of the fill-sheet within the workbook. Index starts with 1
'LENGTHLOCATION As Range                    -> Value that identifies the row where the length attributes are stored
'DataOffset As Range                        -> Value to identify the first cell with data
'SETTINGS_ColorNumeric As Integer           -> Value for formatting color
'errorsNumeric As  Integer                  -> Amount of errors within the sheet


Sub CheckNumeric(ROW As Integer, COL As Integer, SETTINGS_IndexOfFillSheet As Integer, LENGTHLOCATION As Range, DataOffset As Range, SETTINGS_ColorNumeric As Integer, errorsNumeric As Integer)
           
   If (ROW >= DataOffset.ROW) And (COL >= DataOffset.Column) Then
               
       'check if column should be numeric or not
                   If Cells(LENGTHLOCATION.ROW + 1, COL) = "number" Then
                       'column is numeric
                                   If Not IsNumeric(Cells(ROW, COL).Value) Then
                                       Call ChangeColor(ROW, COL, SETTINGS_ColorNumeric)
                                       errorsNumeric = errorsNumeric + 1
                                   Else

                                        'WIRD GECALLT, WENN DER VALUE NUMERIC IST
                                        ' HIER MUSS DER STRING UMFORMATIERT WERDEN
                                        'BISHERIGE VERSUCHE:

                                       'Range(Cells(ROW, COL)).NumberFormat = "#,##0.##"
                                       'Range(Cells(ROW, COL).Address(RowAbsolute:=False, ColumnAbsolute:=False)).NumberFormat = "#,##0.##"
                                   End If
                   End If
   End If
End Sub

Leider muss ich JEDEN value innerhalb des Dokumentes im Anschluss in das Format Text formatieren. Deshalb muss ich aktiv den Value der Zelle verändern und nicht einfach das NumberFormat.

 
So ändere ich jeden Value zu Text:

Code:

Sub CheckIsText(ROW As Integer, COL As Integer, SETTINGS_IndexOfFillSheet As Integer, DataOffset As Range, SETTINGS_ColorIsText As Integer, errorsIsText As Integer)
   'if the cells row and column is withing the data range then
               If (ROW >= DataOffset.ROW) And (COL >= DataOffset.Column) Then
                   
                   If Not (Worksheets(SETTINGS_IndexOfFillSheet).Cells(ROW, COL).NumberFormat = "@") Then
                       Worksheets(SETTINGS_IndexOfFillSheet).Cells(ROW, COL).NumberFormat = "@"
                       Call ChangeColor(ROW, COL, SETTINGS_ColorIsText)
                       errorsIsText = errorsIsText + 1
                   End If
               End If
   
End Sub

Habt ihr eine Idee wie ich das angehen kann? 

Habe noch einen Regulären Ausdruck gefunden, kann man sowas einbauen?: /^\$?\-?\d+(\,\d{3})*(\.\d\d)?$/gm



Mit freundlichen Grüßen

Simon

[Wastl]

Hi,

guggschdu
https://www.tippscout.de/excel-punkt-statt-komma.html

[SimonM]

Hi,

guggschdu
https://www.tippscout.de/excel-punkt-statt-komma.html

 Hallo, danke für die Rückmeldung.

Auf diese Einstellung bin ich ebenfalls gestoßen.

Problem ist, dass ich versuche die möglichen Fehler bei Eingabe durch verschiedenste Mitarbeiter zu lokalisieren.

Das heißt es kann vorkommen, dass jemand ein Komma benutzt und zu einem späteren Zeitpunkt einen Punkt als Separator.


Gruß

[SimonM]

Habe es doch hinbekommen den "Parser" zu coden.

Jedoch funktioniert dieser nicht für alle Werte. Das Prinzip habe ich jedoch klar:

Code:

Sub CheckSeparator(ROW As Integer, COL As Integer, SETTINGS_IndexOfFillSheet As Integer, DataOffset As Range, SETTINGS_ColorSeparator As Integer, errorsSeparator As Integer)

       If (ROW >= DataOffset.ROW) And (COL >= DataOffset.Column) Then
       
                       'column is numeric
                                   If IsNumeric(Cells(ROW, COL).Value) Then
                                               'cell contains separators
                                                           If (InStr(Cells(ROW, COL), ".") <> 0) And (InStr(Cells(ROW, COL), ".") <> 0) Then
                                                                   Dim dotPos As Integer
                                                                   dotPos = InStr(Cells(ROW, COL), ".")
                                                                   
                                                                   Dim kommaPos As Integer
                                                                   kommaPos = InStr(Cells(ROW, COL), ",")
                                                                   
                                                                   If dotPos < kommaPos Then
                                                                       'swap
                                                                           
                                                                       Dim temp As String
                                                                       temp = Cells(ROW, COL)
                                                                       temp = Replace(temp, ".", "#")
                                                                       temp = Replace(temp, ",", ".")
                                                                       temp = Replace(temp, "#", ",")
                                                                       
                                                                       
                                                                       Worksheets(SETTINGS_IndexOfFillSheet).Cells(ROW, COL).Value = temp
                                                                           
                                                                   End If
                                                           End If
                                   End If
                   
   End If

End Sub

[Wastl]

für welche werte funktionierts nicht?

[losgehts]

Hallo,

nur als Vorschlag:

In der Datenbank sind doch bestimmt Zahlen ohne Tausender-Trennzeichen erlaubt?!
Dann würde ich alle Texte, die Zahlen repräsentieren, (ohne weitere Abfrage) einfach so behandeln:

Code:

... = replace( ..., Application.ThousandSeparator, "")
... = replace( ..., Application.DecimalSeparator, ".")

Damit bist du unabhängig von den Einstellungen des Users.

Grüße, Ulrich