WorksheetFunction.Index calls error type mismatch run time error 13-CodePudding

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

CodePudding user response：

I can reproduce the Type Mismatch error with Index:

Sub tester()
    
    Dim r As Long, c As Long, res
    Dim arr As Variant
    
    ReDim arr(1 To 10, 1 To 5)
    For r = 1 To UBound(arr, 1)
    For c = 1 To UBound(arr, 2)
        arr(r, c) = "R" & r & "-C" & c
    Next c
    Next r
    
    arr(5, 2) = Null '### ok if you comment this out
    
    res = WorksheetFunction.index(arr, 0, 2) 'Error 13
    
End Sub

Something in your array doesn't work with Index() - here it's Null but your array may contain some other problem type.

CodePudding user response：

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