About Us

David

Thanks JE
Nice and straightforward, love it

"JE McGimpsey" wrote:

One way:

Dim myCollection As Collection
Dim MyArray As Variant
Dim rTemp As Range
Dim r As Long
Dim c As Long
Dim i As Long

Set myCollection = New Collection
Set rTemp = Sheets("MySheet").Range("rng")
MyArray = rTemp.Value
'// Load Array into collection, eliminate dupes
For r = 1 To UBound(MyArray, 1)
For c = 1 To UBound(MyArray, 2)
If Not IsEmpty(MyArray(r, c)) Then
On Error Resume Next
myCollection.Add MyArray(r, c), "Key " & MyArray(r, c)
On Error GoTo 0
End If
Next
Next

For i = 1 To myCollection.Count
Debug.Print myCollection(i), _
Application.CountIf(rTemp, myCollection(i))
Next i

In article ,
David wrote:

Hi,
I'm summarising the contents of a range in a report. Each cell in the range
contains text or is empty. I've loaded the range into a variant array, then
looped thro the array and loaded a NewCollection using the .Add [Key'] arg to
eliminate duplicates. I would now like to loop thro the NewCollection and
count occurrences of each string in the array. What is the neatest way to do
this?
My code:
MyArray = Sheets("MySheet").Range("rng").Value
'// Load Array into collection, eliminate dupes
For r = 1 To UBound(MyArray, 1)
For c = 1 To UBound(MyArray, 2)
If Not IsEmpty(MyArray(r, c)) Then
On Error Resume Next
myCollection.Add MyArray(r, c), "Key " & MyArray(r, c)
On Error GoTo 0
End If
Next
Next

For i = 1 To myCollection.Count
'code required here
Next

Thanks

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
counting occurrences	dpwicz	Excel Discussion (Misc queries)	2	October 2nd 08 06:25 PM
Array problem: Key words-Variant Array, single-element, type mismatch error	davidm	Excel Programming	6	November 9th 05 05:54 AM
Array problem: Key words-Variant Array, single-element, type mismatch error	davidm	Excel Programming	1	November 8th 05 04:21 AM
ReDim Object array as parameter of Variant array	Peter T	Excel Programming	4	May 10th 05 02:11 PM
variant array containing cel adresses convert to actual ranges-array	Peter[_21_]	Excel Programming	5	December 10th 03 09:50 PM

Menu

About Us