Total VBA newbie alert!

I'm a beginner to VBA and I'm working with collections for the first time.

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 fine it's the next part I'm having trouble with.

I want to take each of the prices in the collection and add them together but I have no clue how to do this. Thanks in advance

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

CodePudding user response：

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.