Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection does not work with strings?
Hi all coders out there!
I wanted a function that returned the number of unique elements in a range. And I thought that the best and easyest solution would be using the Collection object. But to my big surprice the solution I came up with only worked with numbers! Option Explicit ' Returns the number of unique values in the given range. ' Only works with numbers. why not strings? Public Function uniqueCountInt(r As Range) Dim tmpR As Range Dim tmpColl As New Collection On Error Resume Next For Each tmpR In r.Cells tmpColl.Item tmpR.Cells.Value If Err.Number < 0 Then tmpColl.Add tmpR.Cells.Value End If Err.Clear Next uniqueCountInt = tmpColl.Count End Function Does any one out there have a tip on the easyest way of making the function work with strings (and numbersc as well if possible)? Is Collection the right way to do it? best regards/ Olaf |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection does not work with strings?
Sub AAAA()
Dim rng As Range Set rng = Range("A1:a15") Debug.Print uniqueCountInt(rng) End Sub Public Function uniqueCountInt(r As Range) Dim tmpR As Range Dim tmpColl As New Collection On Error Resume Next For Each tmpR In r.Cells tmpColl.Add tmpR.Value, CStr(tmpR.Value) Next On Error GoTo 0 uniqueCountInt = tmpColl.Count End Function Worked fine for me with strings. Note that is was case insensitive. -- Regards, Tom Ogilvy "Olaf" wrote in message om... Hi all coders out there! I wanted a function that returned the number of unique elements in a range. And I thought that the best and easyest solution would be using the Collection object. But to my big surprice the solution I came up with only worked with numbers! Option Explicit ' Returns the number of unique values in the given range. ' Only works with numbers. why not strings? Public Function uniqueCountInt(r As Range) Dim tmpR As Range Dim tmpColl As New Collection On Error Resume Next For Each tmpR In r.Cells tmpColl.Item tmpR.Cells.Value If Err.Number < 0 Then tmpColl.Add tmpR.Cells.Value End If Err.Clear Next uniqueCountInt = tmpColl.Count End Function Does any one out there have a tip on the easyest way of making the function work with strings (and numbersc as well if possible)? Is Collection the right way to do it? best regards/ Olaf |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collection does not work with strings?
Olaf,
You need to add the string value as a key. Indeed the key only accepts string, not numeric variable (unless you CStr first) From the help file: MyClasses.Add item := Inst, key := CStr(Num) NickHK "Olaf" wrote in message om... Hi all coders out there! I wanted a function that returned the number of unique elements in a range. And I thought that the best and easyest solution would be using the Collection object. But to my big surprice the solution I came up with only worked with numbers! Option Explicit ' Returns the number of unique values in the given range. ' Only works with numbers. why not strings? Public Function uniqueCountInt(r As Range) Dim tmpR As Range Dim tmpColl As New Collection On Error Resume Next For Each tmpR In r.Cells tmpColl.Item tmpR.Cells.Value If Err.Number < 0 Then tmpColl.Add tmpR.Cells.Value End If Err.Clear Next uniqueCountInt = tmpColl.Count End Function Does any one out there have a tip on the easyest way of making the function work with strings (and numbersc as well if possible)? Is Collection the right way to do it? best regards/ Olaf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
CONCATENATE I have two text strings in cells but it wont work | Excel Discussion (Misc queries) | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
work with strings/numbers and dates | Excel Programming | |||
Using a collection class to implement mutliple find/replace strings in cells | Excel Programming |