VBA pasting array date values in different format

Question

I have a simple macro that includes a dynamic array which fills up upon conditions are met. The data populates the macro as it is supposed to be functioning until it paste the data onto the spreadsheet. Now, all data is pasted correctly except for date values. Date values are pasted erroneously from European format to American format onto the spreadsheet (i.e. dd-mm-yyyy to mm-dd-yyyy). So for instance a 1st march 2019 becomes a 3rd January 2019 on the spreadsheet. Note that either I format beforehand the destination files or not, the problem still occurs.

The array has 14 columns and only column 12-13 are date values.

Edit Summary

Shredded the code from irrelevant information; added images of results.

Following is the code

Sub Verification()
    Dim NewWorkbook As String, NewWorksheet As String
    Dim wb As Workbook, sh As Worksheet
    Dim LoopCounter As Long
    Dim NewEntryCounter As Long
    Dim Cols As Long, Rows As Long
    Dim r As Range
    Dim arr As Variant, NewEntry() As Variant
    Dim myRange As Integer

    NewWorkbook = LCase(InputBox("What is the name of the new report?"))
    NewWorksheet = LCase(InputBox("What is the name of the sheet?"))
    Set wb = ThisWorkbook
    Set sh = wb.Sheets("Renouvellement")

        Cols = Workbooks(NewWorkbook).Sheets(NewWorksheet).Range(Workbooks(NewWorkbook).Sheets(NewWorksheet).Cells(1, 1), Workbooks(NewWorkbook).Sheets(NewWorksheet).Cells(1, 1).End(xlToRight)).Count
        Rows = sh.Range(sh.Cells(1, 1), sh.Cells(1, 1).End(xlDown)).Count

        For Each r In Workbooks(NewWorkbook).Sheets(NewWorksheet).Range("A2", Workbooks(NewWorkbook).Sheets(NewWorksheet).Range("A1").End(xlDown))
        If (r.Offset(0, 21).Text = "Red" Or r.Offset(0, 21).Text = "Blue") And r.Offset(0, 17).Value >= 24 Then
            arr = Application.VLookup(r.Value, sh.Range("A:A"), 1, 0)

            If IsError(arr) Then
                NewEntryCounter = NewEntryCounter + 1
                ReDim Preserve NewEntry(1 To Cols, 1 To NewEntryCounter)
                For LoopCounter = 1 To Cols
                    NewEntry(LoopCounter, NewEntryCounter) = r.Offset(0, LoopCounter - 1)
                Next LoopCounter
            Else

End Sub

Sample results from Local window

Sample results when transferring date values onto spreadsheet

As you can see the first value inserted is changed when transferring data from vba to spreadsheet. The second value is correctly transferred. The third is not, and so on.

What is the format of the destination column to which the dates are being pasted? What are your Windows Regional Settings short date format (I suspect DMY or similar for that). — Ron Rosenfeld
The dates that do not change would not be valid dates if reversed (there's no 13th month, for example, on that second data point). That explains why some dates change. As mentioned in other comments, how the data is displayed is a function of the formatting applied to the cells. This has nothing to do with the code - the information is being carried over correctly. After the macro runs, select this column then go to Format/Format Cells. Look in the "Number" tab and you'll probably see that the "Locale" is not what you think it should be. Change the locale and see if that makes a difference? — Cindy Meister
@CindyMeister I think you got it right. The values get messed up once pasted on the spreadsheet. I checked and the days above the 13ths are stored as general format — Alessio_110
@RonRosenfeld I cannot access to Windows Regional Settings as I do not have permission — Alessio_110
Update: If I insert on an Excel cell a random date such as 01/07/2018 I encounter the error "Ambiguous name detected: Verification date" — Alessio_110

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2019-07-05T11:27:32

Again, it's a bit difficult for me to understand exactly what you're doing, but it seems that a filter might be simpler, so far as the copying of relevant data is concerned.

In your code, you are making multiple calls to the worksheet, and multiple Redim Preserve operations on the VBA array. Those operations can be costly.

Perhaps that part of the code could be simplified (and sped up) with something like (obviously, you may need to change the worksheet and range variables):

Set ws = Worksheets("sheet1")
Set r = ws.Range("a1").CurrentRegion
With r
    .AutoFilter field:=22, Criteria1:="red", Operator:=xlOr, Criteria2:="blue"
    .AutoFilter field:=18, Criteria1:=">=24"
End With

r.SpecialCells(xlCellTypeVisible).Copy

'Paste somewhere

ws.ShowAllData

VBA pasting array date values in different format

1 Answers