WorksheetFunction.Index calls error type mismatch run time error 13

Question

oTemplateStartRow = 3

arrLast is array with (1 to 6385, 1 to 6) dimension

I have these code line: Trying to get only second column of array and paste this column into second column of activesheet

Cells(oTemplateStartRow, 2).Resize(UBound(arrLast, 1), 1).Value = WorksheetFunction.Index(arrLast, 0, 2)

It calls error:

type mismatch run time error 13.

What is reason for that?

PS: This test procedure works fine but when I work with my variables (arrLast, oTemplateStartRow ) it is not working:

Sub PartOfTheArray()
Dim ar(1 To 20, 1 To 5)
Dim i As Long, j As Long
    For i = 1 To 20
        For j = 1 To 5
            ar(i, j) = i + j * 0.01
        Next j
    Next i
    Cells(1, 1).Resize(20, 1).Value = ar ' столбец 1 '
    Cells(1, 2).Resize(20, 1).Value = WorksheetFunction.Index(ar, 0, 2) ' столбец 2 '
    Cells(1, 3).Resize(10, 1).Value = WorksheetFunction.Index(ar, 0, 4) ' неполный столбец 4 '
    Cells(1, 4).Resize(1, 5).Value = ar ' строка 1 '
    Cells(2, 4).Resize(1, 5).Value = WorksheetFunction.Index(ar, 7, 0) ' строка 7 '
    Cells(3, 4).Resize(1, 3).Value = WorksheetFunction.Index(ar, 9, 0) ' неполная строка 9 '
End Sub

Answer 1

To test what type of value in your arrLast that breaks your code test this in your code:

    With Worksheets(XPList)
         'Проходим по столцбам Артикул, Наименование  и записываем данные
         .Cells(oTemplateStartRow, 1).Resize(UBound(arrLast, 1), 1).Value = arrLast

'###ADDED:
         for i=1 To Ubound(arrLast,1)
            debug.print i, TypeName(arrLast(i,2))
            if TypeName(arrLast(i,2))="TestType" then arrLast(i,2)="X"
         next

         .Cells(oTemplateStartRow, 2).Resize(UBound(arrLast, 1), 1).Value = WorksheetFunction.Index(arrLast, 0, 2) '>>>error
         
         
          
         'Проходим по всем столбцам блока текущий и записываем данные (столбцы Контрагенты и Итого)
         .Cells(oTemplateStartRow, CurrentBlockStartColumn).Resize(UBound(arrLast, 1), 1).Value2 = WorksheetFunction.Index(arrLast, 0, 3) '>>>error
         .Cells(oTemplateStartRow, CurrentBlockStartColumn + 1).Resize(UBound(arrLast, 1), 1).Value2 = WorksheetFunction.Index(arrLast, 0, 4) '>>>error
         .Cells(oTemplateStartRow, CurrentBlockStartColumn + 2).Resize(UBound(arrLast, 1), 1).Value2 = WorksheetFunction.Index(arrLast, 0, 5) '>>>error
         .Cells(oTemplateStartRow, CurrentBlockStartColumn + 3).Resize(UBound(arrLast, 1), 1).Value2 = WorksheetFunction.Index(arrLast, 0, arrExistTotalColumn) '>>>error
    End With

What output will that give you? Then evaluate removing all values of this type by replacing "TestType" with the TypeName given and see if it works. /J