Exceltabelle: Kommas und Punkte durch Doppelpunkte ersetzen

Question

Guten Morgen,
ich habe folgendes Problem. Ich habe eine Tabelle in der Arbeitszeiten eingetragen werden müssen. Also einen Arbeitszeitnachweis in der die gearbeiteten Stunden eines Monats eingetragen werden. Diese Zeiten sollen immer mit einem ":"(Doppelpunkt) eingetragen werden zb. 118:30 (118 Std : 30 Min). Leider passiert es öfter das Personen diese Zeiten mit "," (Komma= oder "."(Punkt) angeben. Ist es möglich das ich diese Zeichen über ein Makro oder eine andere Methode in ":" (Doppelpunkte) umwandeln kann? Das Blatt hat Blattschutz und über die Auto-Korrektur, Ersetzen - Durch geht es auch nicht da es dann allg gespeichert und für jedes Excelblatt verwendet wird.
Kann mir jmd weiterhelfen?
Danke im vorraus.

Greetz Chris

Answer 1

Hallo,

Leider sind die Angaben etwas ungenau, da bei Blattschutz normalerweise auch nichts einzutragen ist.

Bei Ersetzen Durch, kann man auch die Änderungen auf Spalten oder Zeilen begrenzen.

Ersetzen - Durch geht es auch nicht da es dann allg gespeichert und für jedes Excelblatt verwendet wird.

den Satz verstehe ich nicht

Gruß

Helmut

Answer 2

Hallo Chris,

bei mir werden Eingabenmit Punkt sofort in ein Datum umgewandelt nach Enter.

Option Explicit ' Variablendefinition erforderlich

Private Sub Worksheet_Change(ByVal Target As Range)
'**************************************************
'* H. Ziplies *
'* 02.11.10 *
'* http://Hajo-Excel.de/ *
'**************************************************
Dim RaBereich As Range, RaZelle As Range
' Bereich der Wirksamkeit
Set RaBereich = Range("L22:M39, O21:O26")
' noch mehr Bereiche
'Set RaBereich = Union(Range("C11:AG11 , C13:AG13, C15:AG15 , C17:AG17 , C19:AG19"), _
Range("C35:AE35, C37:AE37, C43:AG43, C45:AG45 , C47:AG47 , C49:AG49 ,C51:AG51"), _
Range("C67:AF67 , C69:AF69 , C75:AG75 , C77:AG77 , C79:AG79 , C81:AG81"), _
Range("C99:AF99 , C101:AF101, C107:AG107 , C109:AG109 , C111:AG111"), _
Range("C127:AG127 , C129:AG129 , C131:AG131 , C133:AG133 , C139:AF139"), _
Range("C155:AG155, C157:AG157 , C159:AG159 , C161:AG161 , C163:AG163"), _
Range("C179:AF179 , C181:AF181, C187:AG187 , C189:AG189 , C191:AG191"))
' damit werden nur noch die Zellen bearbeitet die im vorgegeben Bereich liegen
' jede Zelladresse ist einzeln angegeben
Set RaBereich = Intersect(RaBereich, Range(Target.Address))
If Not RaBereich Is Nothing Then
Application.EnableEvents = False
ActiveSheet.Unprotect ("Passwort")
For Each RaZelle In RaBereich
RaZelle = Application.Substitute(Application.Substitute(RaZelle, ",", ":"), ".", ":")
Next RaZelle
ActiveSheet.Protect ("Passwort")
Application.EnableEvents = True
End If
Set RaBereich = Nothing
End Sub

Gruß hajo

Answer 3

Hallo Helmut,
der Blattschutz ist nun nicht mehr das Problem. Mit Ersetzen - Durch meinte ich die Option bei Extras - Autokorrektur-Optionen. Das Ersetzen - Durch was du meinst ist ja über die Schaltfläche "Bearebeiten" in der Menüleiste zu erreichen. Dies bringt mir allerdings nichts da der Vorgang ja automatisch ablaufen soll.

Greetz Cjris

Answer 4

Danke für die Hilfe Hajo, werde es nach Mittag gleich mal ausprobieren.

Greetz Chris

Answer 5

Hallo Hajo,
Dein Beispiel bekomme ich irgendwie nicht richtig umgesetzt.
Könntest du mir mal ein Beispiel für den Bereich C10:N10 machen, indem Punkte und Kommas in Doppelpunkte umgewandelt werden. Die Angaben stehen wie oben gesagt immer für Uhrzeiten.
Falls das nicht zu viel Arbeit macht und du Zeit haben solltest wäre es super wenn du mir da weiterhelfen könntest.

Greetz Chris

Answer 6

Hallo Chris,

Du hast meinen Beitrag schon gelsen, ich glaube nicht. Da steht
' Bereich der Wirksamkeit
Set RaBereich = Range("L22:M39, O21:O26")
Da sollte es doch kein Probklem sein den Bereich anzupassen.

Gruß hajo

Answer 7

Hi Hajo,
ich habe eine Zeile ausversehen aus dem Code gelöscht und ihn nun einfach nochmal reinkopiert und den Bereich geändert. Nun funktioniert es. Ich dedanke mich ;)

Gruß Chris

Answer 8

Hi jetzt passiert nun folgendes.

Gibt man eine Stundenzahl ein zum Beispiel 145 Stunden und 5 Minuten

Also 145,05 Schreibt er das richtig als 145:05

Möchte ich aber 145 Stunden und 50 Minuten eingeben, sprich 145,50

Schreibt er mir auch 145:05 hin, was ja nicht korrekt ist.



Dann nimmt er mir keine glatten Stunden mehr.

Zum Beispiel 39

Ich schreibe rein: 39 oder 39,0 oder 39,00

Und er schreibt rein 936:00

936h sind ja 39 Tage das habe ich schon rausgefunden :)

Kann mir jemand dies bezüglich noch helfen?

Answer 9

Hallo Chris,

ich mußte jetzt auch erstmal ein wenig testen.

Option Explicit ' Variablendefinition erforderlich

Private Sub Worksheet_Change(ByVal Target As Range)
'**************************************************
'* H. Ziplies *
'* 02.11.10 *
'* http://Hajo-Excel.de/ *
'**************************************************
Dim RaBereich As Range, RaZelle As Range
' Bereich der Wirksamkeit
Set RaBereich = Range("L22:M39, O21:O26")
' noch mehr Bereiche
'Set RaBereich = Union(Range("C11:AG11 , C13:AG13, C15:AG15 , C17:AG17 , C19:AG19"), _
Range("C35:AE35, C37:AE37, C43:AG43, C45:AG45 , C47:AG47 , C49:AG49 ,C51:AG51"), _
Range("C67:AF67 , C69:AF69 , C75:AG75 , C77:AG77 , C79:AG79 , C81:AG81"), _
Range("C99:AF99 , C101:AF101, C107:AG107 , C109:AG109 , C111:AG111"), _
Range("C127:AG127 , C129:AG129 , C131:AG131 , C133:AG133 , C139:AF139"), _
Range("C155:AG155, C157:AG157 , C159:AG159 , C161:AG161 , C163:AG163"), _
Range("C179:AF179 , C181:AF181, C187:AG187 , C189:AG189 , C191:AG191"))
' damit werden nur noch die Zellen bearbeitet die im vorgegeben Bereich liegen
' jede Zelladresse ist einzeln angegeben
Set RaBereich = Intersect(RaBereich, Range(Target.Address))
If Not RaBereich Is Nothing Then
Application.EnableEvents = False
ActiveSheet.Unprotect ("Passwort")
For Each RaZelle In RaBereich
If InStr(RaZelle, ",") > 0 Then
RaZelle = CDate(Left(RaZelle, InStr(RaZelle, ",") - 1) & ":" & Mid(RaZelle & 0, InStr(RaZelle, ",") + 1, 2))
ElseIf InStr(RaZelle, ".") > 0 Then
RaZelle = CDate(Left(RaZelle, InStr(RaZelle, ".") - 1) & ":" & Mid(RaZelle & 0, InStr(RaZelle, ".") + 1, 2))
End If
RaZelle.NumberFormat = "hh:mm"
Next RaZelle
ActiveSheet.Protect ("Passwort")
Application.EnableEvents = True
End If
Set RaBereich = Nothing
End Sub

Gruß Hajo

Answer 10

Hallo Chris,

wäre nicht einfacher es in Zukunft so einzurichten, dass Eingaben mit Komma, Punkt oder Sonstiges gar nicht möglich sind.


schöne Grüße

Paul1