Sum a collection

[EXCELMACROS]

Hi, I need a macro that counts different items from a range, for example,
column A:
apples
organges
apples
grapes
grapes
grapes
apples

So the Macro will storage values as Apples = 3, Grapes = 3, Oranges = 1
kind of like a countif function,

thanks,

 

[Bernie Deitrick]

Here's one way:

HTH,
Bernie
MS Excel MVP

'Add a reference to Microsoft Scripting Runtime.
'Add a standard module to the project.
'Insert the following code into the Module.

Sub Main()
Dim Dict As Dictionary
Dim ItemCount As Integer
Dim myC As Range

Set Dict = New Dictionary
With Dict
'set compare mode
.CompareMode = BinaryCompare

'add items from column A, starting in row 1
Dim myR As Range
Set myR = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

For Each myC In myR
If Not .Exists(myC.Value) Then
myVal = Application.CountIf(myR, myC.Value)
.Add Key:=myC.Value, Item:=myVal
myTotal = myTotal + myVal
If myTotal = myR.Cells.Count Then GoTo FoundAll
End If
Next myC

FoundAll:
'extract keys into variant array
keyArray = .Keys
MsgBox "There are " & Dict.Count & " items in your list."
For Each element In keyArray
MsgBox element & " - " & .Item(element)
Next

End With
Set Dict = Nothing
End Sub