VBA Converting column in table to array results in different types

Question

I am trying to convert a column from a table into an array and a range into a second array.

Using the same data (I manually copy/pasted) and having them in the same format of custom mmm-yy, I see they have different types once converted into an array.

Dim Wb As Workbook
Dim Table1 As ListObject
Dim array1, array2

Set Wb = ThisWorkbook

Set Table1 = Wb.Worksheets("Sheet1").ListObjects("allDates")
array1 = Application.Transpose(Table1.ListColumns(1).DataBodyRange)

With Wb.Worksheets("Sheet2")
    array2 = .Range(.Cells(2,1), .Cells(2,1000)).Value
End With

For i = 1000 To 1 Step -1
    If IsError(Application.Match(array2(1,i),array1,0)) Then
        rest of code
    End If
Next i

array1 is a 1d array, and has my dates as type Variant/Double. The value for 01/01/2017 is 42736.

array2 is a 2d array, and has my dates as type Variant/Date. The value for 01/01/2017 is #01/01/2017#.

Since I have them formatted as the same and confirm they are the same dates/values, why does one array have it as one type and the other array as another type? How would I go about comparing the values?

Answer 1

There is no need for Transpose or array1 here at all, in fact, they're inefficient. Transpose can only handle a certain number of rows, and Match is slower when looking at an array. Just use the source range:

Dim rng As Range
Set rng = Table1.ListColumns(1).DataBodyRange

For i = 1000 To 1 Step -1
    If IsError(Application.Match(array2(1,i),rng,0) Then
        rest of code
    End If
Next i