Sum Up the Elements of a Collection

I have a userform that has a series of comboboxes (the number of boxes changes depending on another combobox in another userform) and when the user changes the comboboxes and clicks the button, the code looks up the price of each item associated with the selection and passes it to a collection. This part of the code works.

I want to take each of the prices in the collection and add them together.

Private Sub Button_Add_Click()

Dim coll as Collection
Dim priceColl As Collection
Dim item As Variant
Dim i As Long
Dim d As Long
Dim priceDrawer As Integer
Dim tableArray As Range 
Dim ws As Worksheet

Set coll = New Collection
Set priceColl = New Collection
d = Me.cbo_DrawerNumber.Value
Set ws = ThisWorkbook.Sheets("LookupTables")
With ws
    Set tableArray = .Range(.Cells(3, 7), .Cells(100000, 9))
End With

'adds items from combobox to a collection
For i = 1 To d
    item = Me.Controls("ComboBox" & i).Value
    coll.Add item
Next i
 
'looks up price associated with each item and adds it to a different collection
For Each item In coll
    priceDrawer = WorksheetFunction.VLookup(item, tableArray, 3, False)
    priceColl.Add priceDrawer
    MsgBox priceDrawer & vbNewLine
Next item

'here I want to take all the prices in the second collection and add them together. 
' Note, the  number of times in this collection will vary depending on how many 
' comboboxes have shown up in the userform

End Sub

Solution

Sum Up the Elements of a Collection

Dim Item As Variant
Dim Total As Double

For Each Item In priceColl
    If IsNumeric(Item) Then
        Total = Total + Item
    End If
Next Item

But maybe it is simpler just to use the Total variable in your For Each...Next loop right below the line priceColl.Add priceDrawer:
```
Total = Total + priceDrawer
```
if you are sure that it's a number.