Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting occurrences of textString in variant array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting occurrences of textString in variant array
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting occurrences of textString in variant array
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting occurrences of textString in variant array
The routine eliminates duplicates in the *collection*, not the array.
In article , "Bob Phillips" wrote: Surely, if you have eliminated duplicates, they all occur just once. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting occurrences of textString in variant array
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" wrote in message ... The routine eliminates duplicates in the *collection*, not the array. In article , "Bob Phillips" wrote: Surely, if you have eliminated duplicates, they all occur just once. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting occurrences of textString in variant array
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 , "Bob Phillips" wrote: I quote I would now like to loop thro the NewCollection and count occurrences of each string "JE McGimpsey" wrote in message ... The routine eliminates duplicates in the *collection*, not the array. In article , "Bob Phillips" wrote: Surely, if you have eliminated duplicates, they all occur just once. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting occurrences of textString in variant array
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting occurrences of textString in variant array
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting occurrences | Excel Discussion (Misc queries) | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
ReDim Object array as parameter of Variant array | Excel Programming | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming |