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

Surely, if you have eliminated duplicates, they all occur just once.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"David" <(E-Mail Removed)> wrote in message
news:0449084B-AF2B-49ED-AE8D-(E-Mail Removed)...
> 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
>

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 <0449084B-AF2B-49ED-AE8D-(E-Mail Removed)>,
David <(E-Mail Removed)> 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

The routine eliminates duplicates in the *collection*, not the array.

In article <(E-Mail Removed)>,
"Bob Phillips" <(E-Mail Removed)> wrote:

> Surely, if you have eliminated duplicates, they all occur just once.

I quote

I would now like to loop thro the NewCollection and
count occurrences of each string

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JE McGimpsey" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> The routine eliminates duplicates in the *collection*, not the array.
>
> In article <(E-Mail Removed)>,
> "Bob Phillips" <(E-Mail Removed)> wrote:
>
>> Surely, if you have eliminated duplicates, they all occur just once.

ah, but to finish the quote...

> I would now like to loop thro the NewCollection and
> count occurrences of each string in the array.

OK, I'm done... :-)

In article <(E-Mail Removed)>,
"Bob Phillips" <(E-Mail Removed)> wrote:

> I quote
>
> I would now like to loop thro the NewCollection and
> count occurrences of each string
>
> "JE McGimpsey" <(E-Mail Removed)> wrote in message
> news:jemcgimpsey-(E-Mail Removed)...
> > The routine eliminates duplicates in the *collection*, not the array.
> >
> > In article <(E-Mail Removed)>,
> > "Bob Phillips" <(E-Mail Removed)> wrote:
> >
> >> Surely, if you have eliminated duplicates, they all occur just once.

David,
Why not do the count and remove duplicates at the same time ?
I only public variables rather than Property Let/Get for brevity. You can
easily add any other information to the class if needed.

Private Sub CommandButton1_Click()
Call FixDupes(Sheets("Sheet1").Range("rng"))
End Sub

Public Function FixDupes(argRange As Range) As Long
Dim Cell As Range
Dim MyCollection As Collection
Dim Data As cData
Dim i As Long

Set MyCollection = New Collection

For Each Cell In argRange
If Not IsEmpty(Cell.Value) Then
On Error Resume Next
Set Data = New cData
Data.StrValue = Cell.Value
Data.StrCount = 1

MyCollection.Add Data, Cell.Value

If Err.Number > 0 Then
With MyCollection(Cell.Value)
.StrCount = .StrCount + 1
End With
Err.Clear
End If
End If
Next

For i = 1 To MyCollection.Count
Debug.Print MyCollection(i).StrValue, MyCollection(i).StrCount
Next

End Function

<Class Module; cData>
Public StrValue As String
Public StrCount As Long
</Class Module; cData>

NickHK

"David" <(E-Mail Removed)> wrote in message
news:0449084B-AF2B-49ED-AE8D-(E-Mail Removed)...
> 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
>

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 <0449084B-AF2B-49ED-AE8D-(E-Mail Removed)>,
> David <(E-Mail Removed)> 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
>