Month and Day in a Date are Sometimes Reversed when Copied in Excel

user_8861443 0
Viewed 1950

My Problem:

I have made a For Each loop, which Loops through 2 different columns in the same sheet.
This loop copies the desired columns, and the unique values.
Althought the macro works, it will paste a wrong date.
How it pastes a wrong date, is specified under the section "Goal:".

My Table:

| Date | ColB | Amount | Date | ColG | Amount |
| 02-08-2018 | V584753 | 500 | 02-08-2018 | V584753 | -500 |
| 11-08-2018 | 486542 | 1.000 | 21-08-2018 | 439857 | -30.547 |
| 21-08-2018 | 439857 | 30.547 | 31-08-2018 | V587742 | -1.059 |

My Code:

Sub PasteCellsWithoutMatch()

Dim ColB As Range, ColG As Range, c As Range
Dim Wf As WorksheetFunction
Set Wf = WorksheetFunction
Dim vR() As Variant
Dim k As Long, j As Integer

For Each c In ColB.Cells
    If Not IsEmpty(c) Then
        With Wf
            n = .CountIfs(ColG, c)
            If n = 0 Then
                k = k + 1
                ReDim Preserve vR(1 To 3, 1 To k)
                For j = 1 To 3
                    vR(j, k) = c.Offset(0, j - 2)
                Next j
            End If
        End With
    End If
Next

Sheet("Match").Range("A1").Resize(k, 3) = Wf.Transpose(vR)

Goal:

What I mean by "pasting the wrong date", the code will swaps the "dd-mm-yyyy" to "mm-dd-yyyy" whenever the first value is below 12.

Meaning that: 02-08-2018
Becomes: 08-02-2018

Whereas: 13-08-2018
Remains: 13-08-2018

How can I correct this error?
If there is any correction to it.

Thank you in advance, for your help.

transpose vba excel loops
3 Answers

Replicating the problem with minimal code:

Public Sub TestMe()

    Dim val1 As String: val1 = "02/08/2018"
    Dim val2 As String: val2 = "13/08/2018"
    Dim myInput1 As Date
    Dim myInput2 As Date

    myInput1 = val1
    myInput2 = val2

    Range("A1") = myInput1
    Range("A2") = myInput2

End Sub

Gets this:

enter image description here

One possible solution is using DateSerial():

Public Sub TestMe()

    Dim val1 As String: val1 = "02/08/2018"
    Dim val2 As String: val2 = "13/08/2018"
    Dim myInput1 As Date
    Dim myInput2 As Date

    myInput1 = DateSerial(Split(val1, "/")(2), Split(val1, "/")(1), Split(val1, "/")(0))
    myInput2 = DateSerial(Split(val2, "/")(2), Split(val2, "/")(1), Split(val2, "/")(0))

    Range("A1") = myInput1
    Range("A2") = myInput2

End Sub

For some advanced solution, you may consider either writing a customized function or changing a bit the regional settings. The latter is dangerous.

user_54486260

It depends on your local settings. Anyway, macro doesn't look into local at all. So you can overpass it by using format function. For example:

val1="02/08/2018"
val2=format(cdate(val1),"ddmmyyyy"))

It's gonna work

user_89303870

I found a solution myself.

First of all, thank you for you efforts, but I couldn't really get myself to try them, since I needed to change to much in my formula...

Before looping through the cells, I removed the date format from the date columns. Whereas turning it into to general, the transpose won't swap the numbers around.

RngNavRange = "A3:A" & LastRowA
RngKvikRange = "F3:F" & LastRowF

Range(RngNavRange).NumberFormat = "General"
Range(RngKvikRange).NumberFormat = "General"

user_88614430
Related