VBA错误13（键入不匹配）试图将项目添加到集合中

Question

我有2个包含名称的范围取2个数组。我想创建一个仅在数组2中的数组1中的名称中的第三个数组。但是，当试图将值添加到 collection 时，不匹配类型错误。

这是整个代码：

Sub CrearArreglos()

'**Array2**
Dim Array2() As Variant 
Dim iCountLI As Long 
Dim iElementLI As Long 

If IsEmpty(Range("B3").Value) = True Then
      ReDim Array2(0, 0)
Else
      iCountLI = (Sheets("Sheet2").Range("B3").End(xlDown).Row) - 2
      iCountLI = (Range("B3").End(xlDown).Row) - 2 
      ReDim Array2(iCountLI) 

      For iElementLI = 1 To iCountLI 
      Array2(iElementLI - 1) = Cells(iElementLI + 2, 2).Value 
      Next iElementLI
      
End If

'**Array 1:**
Dim Array1() As Variant 
Dim iElementLC As Long

Worksheets("Sheet1").Activate
Array1 = Worksheets("Sheet1").Range("BD4:BD10").Value 

Dim v3 As Variant
Dim coll As Collection
Dim i As Long

'**Extracting values from Array 1 that are not contained in Array 2**
Set coll = New Collection

For i = LBound(Array1, 1) To UBound(Array1, 1)
    If Array1(i, 1) <> 0 Then
'**This line below displays error 13 ↓
    coll.Add Array1(i, 1), Array1(i, 1)
    End If
Next i

For i = LBound(Array2, 1) To UBound(Array2, 1)
    On Error Resume Next
    coll.Add Array2(i, 1), Array2(i, 1) 
    If Err.Number <> 0 Then
        coll.Remove Array2(i, 1)
    End If
    If coll.exists(Array2(i, 1)) Then
        coll.Remove Array2(i, 1)
    End If
    On Error GoTo 0
Next i


ReDim v3(0 To (coll.Count) - 1)

'Adds collection items to a new array:
For i = LBound(v3) To UBound(v3)
    v3(i) = coll(i + 1) 
    Debug.Print v3(i)
Next i

因此，这是显示错误13的行。如果我删除了第二个“ array1（i，1）”，则运行良好，但它仅保存从array1中保存所有值，并且似乎忽略了代码的其余部分和条件）

coll.Add Array1(i, 1), Array1(i, 1)

奇怪的是，此代码始终工作过去的两个范围在同一张纸上时，在过去。这次，我从不同的床单中拿出范围。我不知道这是否是问题，尽管对我来说没有意义。

感谢任何帮助。先感谢您！

Answer 1

这应该与您打算做同样的事情，并且表现良好：

Sub CrearArreglos()

    Dim Array1, col As New Collection, i As Long, rng2 As Range, arrOut, v
    
    With Sheets("Sheet2")
        Set rng2 = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp))
    End With
    
    Array1 = Worksheets("Sheet1").Range("BD4:BD10").Value
    
    For i = 1 To UBound(Array1, 1)
        v = Array1(i, 1)
        If Len(v) > 0 Then 'anything to check?
            'this check is faster against a range...
            If IsError(Application.Match(v, rng2, 0)) Then col.Add v
        End If
    Next i
    
    ReDim arrOut(0 To col.Count - 1)
    For i = 1 To col.Count
        arrOut(i - 1) = col(i)
    Next i
    
End Sub