Excel Date Format is getting changed after running VBA code

[bobbyexcel]

Hi, Can someone help me on my below issue please.

I've my data from Col A to F. Trying to remove duplicates from Col A to D and copy the non duplicate data in to E with a delimator.

Now the issue is whenever I run the script, the first 12 days of any month is converted from dd-mm-yyyy to mm-dd-yyyy. My base data is fine and there is no issues. But the only problem is with the script. Could anyone please help me.

Have a look at my data after running the script..

        Dim lr&, i&, rng, name As String, arr(), key
        Dim dic As Object
        Set dic = CreateObject("Scripting.dictionary")
        lr = Cells(Rows.Count, "A").End(xlUp).Row
        rng = Range("A2:F" & lr).Value

    For i = 1 To lr - 1
        name = rng(i, 1) & "|" & rng(i, 2) & "|" & rng(i, 3) & "|" & rng(i, 4)
        If Not dic.exists(name) Then
            dic.Add name, rng(i, 6) & ";" & rng(i, 5)
        Else
            dic(name) = rng(i, 6) & "," & dic(name) & "," & rng(i, 5)
        End If
    Next

    Range("A2:F" & lr).ClearContents
    ReDim arr(1 To dic.Count, 1 To 6)
    i = 0
        For Each key In dic.keys
            i = i + 1
            arr(i, 1) = Split(key, "|")(0)
            arr(i, 2) = Split(key, "|")(1)
            arr(i, 3) = Split(key, "|")(2)
            arr(i, 4) = Split(key, "|")(3)
            arr(i, 5) = dic(key)
        Next

        Range("A2").Resize(i, 6).Value = arr

 

[6StringJazzer]

My base data is fine

Sorry, your base data is not fine. Your date column has text, not dates. When you try to capture that text and write them back, Excel tries to interpret them as dates. So VBA tries to write 01-06-2022 to a cell and Excel thinks you mean June 6, 2022, and writes it as date data.

The way to fix this is either:

Convert your existing data from text to actual dates (preferred). Put this in an unused column:

=DATE(RIGHT(D2,4),MID(D2,4,2),LEFT(D2,2))

and fill down. Then copy the results and "paste values" back into column D. Set the display format as desired.

or

Force the data to be written back as text

            arr(i, 4) = "'" & Split(key, "|")(3)

 

[bobbyexcel]

Thanks for your reply but both of the suggestions doesn't seems to be working.. Could you please check again and help me..

and VBA code issue..

 

[bobbyexcel]

@DanteAmor @Rick Rothstein @Peter_SSs @smozgur Can you guys help me on this please if possible..

 

[6StringJazzer]

I don't know how you entered my code but you picked up the formatting tags from the forum. It's just

arr(i, 4) = "'" & Split(key, "|")(3)

Your latest screenshot doesn't seem to match the code. You are showing dates in column G but your code populates A:E.

I need you to be a bit clearer about your data. Please show a sample of what your data looks like before you run any code.

 

[bobbyexcel]

@6StringJazzer which world you are from !! Da*n.. you solved my issue with just one line.. I've modified all my other code and it looks pretty good now. Thank you and you are the savior.

 

[6StringJazzer]

I'm glad it helped, but I'm still not sure it's the best solution. All it does it write back a string as text. But I still think all of your dates need to be date values instead of text.

 

[Peter_SSs]

I don't know how you entered my code but you picked up the formatting tags from the forum.

You had originally used the standard vba code tags which does not hide (or apply) the formatting tags. A moderator subsequently edited your post to use the 'Rich' tags which are designed for when you want to apply specific formatting like you did with the red/bold code.

 

[6StringJazzer]

A moderator subsequently edited your post

Whoops. I usually catch that. Thanks.