Type mismatch in VBA when trying to set array values

Question

I am trying to take an export from AutoCAD and generate a bill of materials(BOM). The output file has an entry for each item placed in the Model (due to extenuating circumstances I can not use AutoCAD's built in BOM building feature). When making the BOM I need to have no part number duplicates and a total quantity.

First I added all of the part numbers to an array. Then I used a function (I did not write) to remove the duplicates. Then I tried to redim my original part number array to be 2D and length of the no duplicates array. Next I wanted to add all values from my no duplicates array back to my original array in the first column. Later I have an idea on how to sum the part numbers and add them to the second column of the array with their matching part numbers.

Here is an example of what I'm given:

CPN:   QTY:
5551    1
5552    3
5551    1
5551    1
5555    6

Here is what I need for output (sorted does not matter)

CPN:    QTY:
5551     3
5552     3
5555     6

And here is the full code I have so far. I get an error at cpns(i, 1) = temp(i)

Sub consolidate()

Dim arrfirst As Integer, arrlast As Integer
Dim cpns() As Variant
Dim CPN_COUNT As Integer

Range("E1000").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
Range("E2:E4").Select
Range("E4").Activate
CPN_COUNT = Selection.Count
ReDim cpns(1 To CPN_COUNT)


For i = 1 To CPN_COUNT
    cpns(i) = Cells(i + 1, 5)
Next

temp = ArrayRemoveDups(cpns)
arrfirst = LBound(temp)
arrlast = UBound(temp)
ReDim cpns(arrfirst To arrlast, arrfirst To arrlast)

For Each i In temp
    cpns(i, 1) = temp(i)
Next


End Sub


Function ArrayRemoveDups(MyArray As Variant) As Variant
    Dim nFirst As Long, nLast As Long, i As Long
    Dim item As String
    
    Dim arrTemp() As String
    Dim Coll As New Collection
 
    'Get First and Last Array Positions
    nFirst = LBound(MyArray)
    nLast = UBound(MyArray)
    ReDim arrTemp(nFirst To nLast)
 
    'Convert Array to String
    For i = nFirst To nLast
        arrTemp(i) = CStr(MyArray(i))
    Next i
    
    'Populate Temporary Collection
    On Error Resume Next
    For i = nFirst To nLast
        Coll.Add arrTemp(i), arrTemp(i)
    Next i
    Err.Clear
    On Error GoTo 0
 
    'Resize Array
    nLast = Coll.Count + nFirst - 1
    ReDim arrTemp(nFirst To nLast)
    
    'Populate Array
    For i = nFirst To nLast
        arrTemp(i) = Coll(i)
    Next i
    
    'Output Array
    ArrayRemoveDups = arrTemp
 
End Function

Any help is appreciated. I'm sure there is a much easier way to do all of this but I'm new to VBA

I should also add the datatype for the cpns at my error point is Variant/Variant and the datatype for temp is Variant/String.

Answer 1

Arrays should be iterated over using For i = Lbound(...) to Ubound(...), not For Each.

Change

For Each i In temp

to

For i = Lbound(temp) to Ubound(temp)

As pointed out in comments, you can use a For Each loop... but just don't.