Microsoft Excel

Herbers Excel/VBA-Archiv

In Datum . durch / ersetzen, kleines Problem


Betrifft: In Datum . durch / ersetzen, kleines Problem
von: Torsten
Geschrieben am: 15.04.2019 09:05:21

Hallo zusammen,

ich habe einen Code aus dem Netz, welcher mir in bestimmten Spalten in einem Datumsstring, der von SAP importiert wird, den Punkt durch einen Schraegstrich ersetzt fuer das UK Format.
Dann aendere ich das Format mit Text to Columns zum Datumsformat.
Das funktioniert auch soweit, ABER, wenn unter der Ueberschriftenzeile nur eine einzige Datenzeile vorhanden ist bekomme ich einen Laufzeitfehler "Type mismatch" in Zeile

ArrayDates = TargetRange.Value

Ab 2 Datenzeilen bekomme ich den Fehler nicht mehr.
Kann mir jemand helfen und mir sagen, wo der Fehler liegt?

Hier der komplette Code:
Dim TargetSheet As Worksheet
    ' Set the correct target sheet here:
    Set TargetSheet = ThisWorkbook.Sheets("Expediting - Overdues")

    Dim LastColRow As Long
    ' Store the absolute last row within a long variable for later use
    LastColRow = TargetSheet.Cells(Rows.Count, 10).End(xlUp).Row

    Dim TargetRange As Range
    ' Assumes your data starts in cell 2 (has a header row). Change the 2 as needed.
    Set TargetRange = TargetSheet.Range("J2:J" & LastColRow)

    Dim ArrayDates() As Variant

    ' Load all the dates into an array for modification
    ArrayDates = TargetRange.Value

    ' Edit the format of the destination to be text based. This will prevent Excel from  _
assuming format
    ' Note: This must be done after the values are put into the array, otherwise you could load  _
values in the
    ' wrong format.
    TargetRange.NumberFormat = "@"

    Dim i As Long
    ' Loop through the array and properly format all of the data
    For i = LBound(ArrayDates, 1) To UBound(ArrayDates, 1)
        ArrayDates(i, 1) = Format(CStr(Replace(ArrayDates(i, 1), ".", "/")), "dd/mm/yyyy")
    Next

    ' Output the modified data
    TargetRange.Value = ArrayDates
    
    TargetRange.Select
    Selection.TextToColumns Destination:=Range("J2"), DataType:=xlFixedWidth, _
        OtherChar:="/", FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Danke im Voraus fuer eure Hilfe.

Gruss Torsten

  

Betrifft: AW: In Datum . durch / ersetzen, kleines Problem
von: Werner
Geschrieben am: 15.04.2019 09:37:04

Hallo Torsten,

warum gehst du überhaupt den "Umweg" über ein Array und ersetzt die Punkte nicht direkt auf dem Blatt?

With Targetsheet
    LastColRow = .Cells(.Rows.Count, 10).End(xlUp).Row
    .Range("J2:J" & LastColRow).Replace What:=".", Replacement:="/", LookAt:=xlPart
End With
Gruß Werner


  

Betrifft: AW: In Datum . durch / ersetzen, kleines Problem
von: Torsten
Geschrieben am: 15.04.2019 09:52:41

Hallo Werner,

vielen Dank fuer den Tip. Funktioniert super und ohne Fehlermeldung.
Hier nun mein endgueltiger Code:

    Dim TargetSheet As Worksheet
    Set TargetSheet = ThisWorkbook.Sheets("Expediting - Overdues")

    Dim LastColRow As Long
    LastColRow = TargetSheet.Cells(Rows.Count, 10).End(xlUp).Row

    With TargetSheet
        LastColRow = .Cells(.Rows.Count, 10).End(xlUp).Row
            With .Range("J2:J" & LastColRow)
                .Replace What:=".", Replacement:="/", LookAt:=xlPart
                .TextToColumns Destination:=Range("J2"), DataType:=xlFixedWidth, OtherChar:="/", _
 FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
            End With
    End With
Gruss Torsten


  

Betrifft: AW: In Datum . durch / ersetzen, kleines Problem
von: Werner
Geschrieben am: 15.04.2019 10:00:33

Hallo Torsten,

hier (fetter Teil) hast du doppelt gemoppelt.

Dim TargetSheet As Worksheet, LastColRow As Long

Set TargetSheet = ThisWorkbook.Sheets("Expediting - Overdues")

LastColRow = TargetSheet.Cells(Rows.Count, 10).End(xlUp).Row

With TargetSheet
   LastColRow = .Cells(.Rows.Count, 10).End(xlUp).Row
   With .Range("J2:J" & LastColRow)
         .Replace What:=".", Replacement:="/", LookAt:=xlPart
         .TextToColumns Destination:=Range("J2"), DataType:=xlFixedWidth, OtherChar:="/", _
          FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
   End With
End With
Und sauber refereinziert wäre das auch nicht, da vor das .Rows.Count auch noch das Blatt müsste.
Deshalb so:
Dim TargetSheet As Worksheet, LastColRow As Long

Set TargetSheet = ThisWorkbook.Sheets("Expediting - Overdues")

With TargetSheet
   LastColRow = .Cells(.Rows.Count, 10).End(xlUp).Row
   With .Range("J2:J" & LastColRow)
         .Replace What:=".", Replacement:="/", LookAt:=xlPart
         .TextToColumns Destination:=Range("J2"), DataType:=xlFixedWidth, OtherChar:="/", _
          FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
   End With
End With
Gruß Werner


  

Betrifft: AW: In Datum . durch / ersetzen, kleines Problem
von: Torsten
Geschrieben am: 15.04.2019 10:23:41

Ah OK. Ja, seh ich jetzt auch.
Vielen Dank nochmals


  

Betrifft: Gerne u. Danke für die Rückmeldung. o.w.T.
von: Werner
Geschrieben am: 15.04.2019 10:24:59




  

Betrifft: Zur Erläuterung, damit du den Fehler verstehts
von: Daniel
Geschrieben am: 15.04.2019 10:18:48

Hallo Torsten
für dein Problem ist Werners Idee die richtige Lösung.
Damit du aber verstehst, wo dein Fehler herkommt:

a) du hast ArrayDates als Datenfeld deklariert, dafür stehen die Klammern: dim ArrayDates()
b) wenn TargetRange nur eine Zelle umfasst, ergibt TargetRange.Value einen Einzelwert, nur wenn TargetRange.Value mehrere Zellen groß ist, ergibt TargetRange.Value ein Array.

Einzelwert und Array passen jetzt aber nicht zusammen und müssen im Code auch unterschiedlich "behandelt" werden.

damit dein Code problemlos funktioniert, müsstest du dann in diesem Fall den Einzelwert in ein Array umwandeln, welches nur einen Wert enthält.
Das müsste man dann so programmieren.

Ersetze das

ArrayDates = TargetRange.Value

durch:
If TargetRange.Cells.Count > 1 then
    ArrayDates = TargetRange.Value
Else
    Redim ArrayDates(1 to 1, 1 to 1)
    ArrayDates(1, 1) = TargetRange.Value
End if
Damit ist dann auch im Falle eines Einzelwertes dein ArrayDates ein zweidimensionales Array und dein weiterer Code funktioniert ohne weiter Anpassungen.

Gruß Daniel


  

Betrifft: AW: Zur Erläuterung, damit du den Fehler verstehts
von: Torsten
Geschrieben am: 15.04.2019 10:22:45

Hallo Daniel,

danke fuer die Erklaerung. Wie gesagt, ich hatte den Code aus dem Netz. Bin mit Arrays noch nicht so fit.


  

Betrifft: AW: Zur Erläuterung, damit du den Fehler verstehts
von: Daniel
Geschrieben am: 15.04.2019 10:42:16

naja, wenn du Code aus dem Netz verwendest, dann ist das immer dein Code, für den du genauso verantwortlich bist, als hättest du ihn selber programmiert!
Das solltest du immer bedenken, wenn du Code aus dem Netz verwendest.
Gruß Daniel