Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
812to816
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
812to816
812to816
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Suchen und Ersetzen mit Formatierung

Suchen und Ersetzen mit Formatierung
24.10.2006 20:59:09
Universal
Hallo Leute,
ich habe eine Spalte mit Datum und Uhrzeit. Allerdings ist dieses Datum im Format "Monat.Tag.Jahr Stunden:Minuten".
Nun möchte ich durch einen Makro dieses Problem lösen. Erschwerend kommt hinzu, dass sich mitten in der Spalte das Datumsformat in "Tag/Monat/Jahr Stunden:Minuten" ändert.
Hierzu nun mein Lösungsansatz:
Spalte "A" markieren. Suchen und ersetzen: Punkt durch Punkt und gleichzeitig das Datumsformat auf "TT.MM.JJJJ hh:mm" festlegen. Anschließend das selbe Spielchen durchführen, jedoch "/" durch Punkt ersetzen und das Format auf "TT.MM.JJJJ hh:mm" setzen.
Der Makrorekorder zeichnet folgenden Code auf:
Columns("A:A").Select
Application.ReplaceFormat.NumberFormat = "d/m/yyyy h:mm"
Selection.Replace What:=".", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True
Application.ReplaceFormat.NumberFormat = "mm/dd/yyyy hh:mm"
ActiveCell.Replace What:="/", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True
Wenn ich diesen Code nun ausführe, bekomme ich eine Fehlermeldung: "Laufzeitfehler 1004; Anwendungs- oder objektdefinierter Fehler."
Wenn ich den Debugger wähle, markiert er mir immer die "...NumberFormat =..."-Zeile. Das Format wird anscheinend durch die englischen Begriffe, "Day", "Month" und "Year" definiert. Kann mir jemand einen Tipp geben - ich finde keine Lösung für dieses Problem?
Danke und Gruß
Uni
Beispiel-Tabelle:
SpalteA
01.13.2006 03:00
01.13.2006 03:15
13/01/2006 03:30
soll umgewandelt werden in
13.01.2006 03:00
13.01.2006 03:15
16.01.2006 03:30

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Suchen und Ersetzen mit Formatierung
25.10.2006 05:41:41
ingUR
Hallo, @universal,
Das Programmierproblem wird vermutlich dadurch verursacht, dass ein ein aufgezeichnetes Makro, welches nachträglich bearbeitet wurde, eingesetzt wird (u.a.: Application.ReplaceFormat.NumberFormat ist eine nicht definierte und daher unzulässige Methode).
Das ursächliche Datumsproblem wird darin zu suchen sein, dass die Angaben 01.13.2006 03:00 und gleichwertige als Text in der Zelle eingetragen stehen, während 13/01/2006 03:30 al Datumsformat erkannt wird und somit vom Datentyp Zahl ist.
Optionell überprüfen kannst Du diese Annahme dadurch, dass Du entweder das Leerzeichen vor der Zahl entdeckst (alle Zellen der Spalte A linksausrichten) oder aber, indem Du in eine Hilfsspalte die Zellenformel =Typ(A1) einträgst und über die darunterliegenden Zellen kopierst. Der Wert 1 steh für eine Zahl, der Wert 2 für Text:
"Luxusanzeige": =WAHL(TYP(A1);"Zahl";"Text")
Wenn meine Annahme stimmt, dann sollte nachfolgende Standardmodul-Prozedur die Formatierung der Spalte A vereinheitlichen, wobei ich allerdings davon ausgehe, das aus dem 13/01/2006 auch der 13.01.2006 entstehen soll und nict der 16.01.2006:
Option Explicit
Sub WandleDatumsFormat()
Dim rngC As Range
For Each rngC In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If Not IsNumeric(rngC.Value) Then
rngC.Replace What:=".", Replacement:="/"
End If
Next
Columns(1).NumberFormat = "mm/dd/yyyy hh:mm"
Range("A1").Select
End Sub

Gruß,
Uwe
Anzeige
AW: Korrektur
25.10.2006 06:07:00
ingUR
Bitte Zeile mit falscher Formatangabe ersetzen:
Columns(1).NumberFormat = "dd/mm/yyyy h:mm"
Gruß!
AW: Suchen und Ersetzen mit Formatierung
25.10.2006 20:39:19
Universal
Guten Abend Uwe,
danke für deine Hilfe! Leider hat es nicht ganz funktioniert. Deine Vermutung mit der Formel =Typ(Zelle) ist richtig. Die Werte mit dem Slash werden mit dem Wert "2" versehen - die anderen mit dem Wert "1". Bis jetzt wandelt er nur die Daten mit Punkt um.
Wie kann ich die "Text-Werte" abfragen? Mir ist klar, dass du die IF-Anweisung so geschrieben hast, dass alle Felder, die nicht numerisch sind bearbeitet werden sollen. Kann man nicht explizit danach fragen?
If "IsText"(rngC.Value) Then ...
Vielen Danke im Voraus!
Gruß
Uni
PS: Du hast natürlich Recht. In meinem Beispiel meinte ich nicht 16.01.2006 03:30, sondern den 13.01.2006 03:30.
Anzeige
AW: Suchen und Ersetzen mit Formatierung
25.10.2006 21:08:52
ingUR
Hallo, @univeral,
eine Abfrage ISTTEXT gibt es m.W. nach in VBA nicht, und sie ist ja auch nicht notwendig, wenn man mit der Negierung des Ergebniswertes eine ausreichende Aussage, nämlich die, das es kein Zahlentyp ist, der in der Zelle sich befindet, erhält.
Einzig bin ich bei meinem Programm davon ausgegangen, das die Textketen die sind, die den Punkt enthalten und der Schrägstrich als Bestandteil eines gültigen Datunswertes betrachtet wird.
Daher änder bitte:
If Not IsNumeric(rngC.Value) Then
with rnG
.NumberFormat = "mm/dd/yyyy hh:mm"
.Replace What:="/", Replacement:="."
end with
End If
Solltes Du damit keine Erfolg haben, so bitte ich dich eine Mappe mit einem Bereichsauschnitt der Spalte A, in dem die Problematik auftritt, hochzuladen. Achte bitte darauf, dass die Formatierung der Zellen in diesem Bereich so sind, wie Du sie auch in Deiner Arbeitsmappe eingesetzt hast.
Gruß,
Uwe
Anzeige
AW: Suchen und Ersetzen mit Formatierung
25.10.2006 21:57:34
Universal
Nochmals danke für deine Hilfe. Dass sieht doch schon besser aus. :) Nun tritt allerdings das Problem auf, dass die Werte mit Punkt im amerikanischen Datumsformat dargestellt werden. Beispiel 13.10.2006 03:30 PM
Zum besseren Verständnis und für dich zum Testen, habe ich die Datei hochgeladen - jedenfalls einen Abschnitt davon.
https://www.herber.de/bbs/user/37662.xls
Einen schönen Abend noch!
Gruß
Uni
AW: Suchen und Ersetzen mit Formatierung
26.10.2006 08:55:16
ingUR
Hallo, @Universal,
Dein Problem wird wohl mit dem Einlesen der Daten erzzeugt (CSV-Datei?) und könnte sich vermutlich dort auch beheben lassen.
Wenn du jedoch den im amerikanischen Format gelieferten Datumswert(!) auf das europäische Format legst, wird dennoch weiter die erste Zahlenangabe als Monat zu interpretieren. Dajedcoh jede Zahl über 12 so nicht in eine gülrigen Datumswert gewandelt werden kann, wird ein solcher eintrag als Textkette betrachtet.
Eine Umformatierung erfordert somit, wie Du ja schon in Deinem Erstbeitrag skizziert hast zwei Umformungen, wobei wichtig ist, das der Zelleninhalt in die neuvormatierte Zelle geschrieben wird.
Hier nun meine VBA-Lösung:
Option Explicit
Sub WandleDatumsFormat()
Dim rngC As Range, t As Date
Dim tt As Integer, mm As Integer, jj As Integer, zeit As Double
Range("A:A").NumberFormat = "dd/mm/yyyy hh:mm"
For Each rngC In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If Not IsNumeric(rngC.Value2) Then
rngC.Replace What:="/", Replacement:="."
t = rngC.Value
rngC = t
Else
t = rngC
mm = Day(t)
tt = Month(t)
jj = Year(t)
zeit = t - Int(t)
t = Str(tt) & "." & Str(mm) & "." & Str(jj)
t = t + CDate(zeit)
rngC = t
End If
Next
Range("A1").Select
End Sub
Sie ersetzt das bisherige Makro gleichen Namens im Standardmodul-Ordner.
Gruß,
Uwe
Anzeige
AW: Suchen und Ersetzen mit Formatierung
26.10.2006 19:09:19
Universal
Suuuuper!!! Vielen Dank! Auf so eine Lösung wäre ich nie gekommen.
Danke, danke, danke.
Eine letzte Frage noch: Wie könnte ich die gefüllten Zellen zählen?
Bsp:
Anzahl = SpalteA - 1(wegen Überschrift)
Später könnte ich die Variable "Anzahl" wo anders einsetzen.
Danke nochmals und Gruß
Uni
AW: Suchen und Ersetzen mit Formatierung
26.10.2006 21:53:25
ingUR
Hallo, @Universal,
Deine "letzte Frage" ist nicht so einfach zu beantworten, wenn man nicht genau erkennt, wie die Datenreihe aufgebaut ist. Daher habe ich ein Arbeitsblatt mit einigen VBA-Ansätzen zur ermittlung von Zeilen-/Spaltenanzahlen, ohne den Anspruch zu erheben hier eine umfassende Sammlung zu liefern.
https://www.herber.de/bbs/user/37689.xls
Vielleicht hilft Dir der eine oder andere Ansatz bei der Ermittlung von Zeilen-/Spaltenanzahlen oder der Anzahl von belegten/freien Zellen in einr Saplte bzw. Zeile.
Gruß,
Uwe
Anzeige
AW: Suchen und Ersetzen mit Formatierung
26.10.2006 22:14:59
Universal
ui, ich hoffe, dass ich dir nicht zu viele Umstände mit dieser letzten Frage bereitet habe. Ich habe auch eine Lösung gefunden. Ich hatte den Makrorekorder genommen und mal getestet, was er "aufnimmt". Danach habe ich ein bisschen gebastelt. Mein Code:
Anzahl = Application.CountA(ActiveSheet.Range("A2:A65536").Cells)
MsgBox Anzahl
Danke nochmal! Gute Nacht! :)

314 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige