Clever-Excel-Forum

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

Hi,

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

(22.03.2018, 10:45)Wastl schrieb: [ -> ]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ß

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

für welche werte funktionierts nicht?

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

SimonM

Wastl

SimonM

SimonM

Wastl

losgehts