Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Unique Items to a Collection
Hello,
I'm using the technique described in "Microsoft Excel 2000 - Power Programming with VBA" to create a list of unique items out of a total list that might contain duplications. The technique uses the same value being added to the collection as the key for the collection. Thus when a duplicate value is encountered and error will occur. Using "On Error", the attempt to add a duplicate record is ignored and the next record is processed. ----------------------------- On Error GoTo SkipRecord: For RC = 1 To myTotalRecordCount myUniqueRecordsCollection.Add myTotalRecordsCollection(RC), CStr(myTotalRecordsCollection(RC)) myUniqueRecordsIndices.Add RC, CStr(RC) SkipRecord: Next RC ----------------------------- I happen to know that my first, second, and ninth records are unique and the rest are duplicates. The procedure executes fine, adding records 1 and 2 and their indices to the respective collections, skipping record 3 and then failing at RC = 4. Records 3 and 4 are both identical to record 2. The error is the duplicate key error, the exact error I'm trying to ignore as far as I can tell: ----------------------------- Run-time error '457': This key is already associated with an element of this collection ----------------------------- It almost acts as if it has forgotten the command, "On Error GoTo SkipRecord" between RC =3 and RC=4. Does anyone have any advice? Thanks, WJ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Unique Items to a Collection
From XL/VBA Help ("On Error Statement"):
an "active" error handler is an enabled handler that is in the process of handling an error. If an error occurs while an error handler is active (between the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit Property statement), the current procedure's error handler can't handle the error. So one way: On Error GoTo ErrHandler For RC = 1 To myTotalRecordCount myUniqueRecordsCollection.Add _ myTotalRecordsCollection(RC), _ CStr(myTotalRecordsCollection(RC)) myUniqueRecordsIndices.Add RC, CStr(RC) SkipRecord: Next RC 'remainder of your code Exit Sub ErrHandler: Resume SkipRecord End Sub In article , "WJ" wrote: Hello, I'm using the technique described in "Microsoft Excel 2000 - Power Programming with VBA" to create a list of unique items out of a total list that might contain duplications. The technique uses the same value being added to the collection as the key for the collection. Thus when a duplicate value is encountered and error will occur. Using "On Error", the attempt to add a duplicate record is ignored and the next record is processed. ----------------------------- On Error GoTo SkipRecord: For RC = 1 To myTotalRecordCount myUniqueRecordsCollection.Add myTotalRecordsCollection(RC), CStr(myTotalRecordsCollection(RC)) myUniqueRecordsIndices.Add RC, CStr(RC) SkipRecord: Next RC ----------------------------- I happen to know that my first, second, and ninth records are unique and the rest are duplicates. The procedure executes fine, adding records 1 and 2 and their indices to the respective collections, skipping record 3 and then failing at RC = 4. Records 3 and 4 are both identical to record 2. The error is the duplicate key error, the exact error I'm trying to ignore as far as I can tell: ----------------------------- Run-time error '457': This key is already associated with an element of this collection ----------------------------- It almost acts as if it has forgotten the command, "On Error GoTo SkipRecord" between RC =3 and RC=4. Does anyone have any advice? Thanks, WJ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding Unique Items to a Collection
Yes!!!!!! My problem was that "Next RC" was part of my error handler! The
error handler was already active after RC = 3 and so could not reactivate when it encountered another error on RC = 4. Thanks so much for your help! "JE McGimpsey" wrote in message ... From XL/VBA Help ("On Error Statement"): an "active" error handler is an enabled handler that is in the process of handling an error. If an error occurs while an error handler is active (between the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit Property statement), the current procedure's error handler can't handle the error. So one way: On Error GoTo ErrHandler For RC = 1 To myTotalRecordCount myUniqueRecordsCollection.Add _ myTotalRecordsCollection(RC), _ CStr(myTotalRecordsCollection(RC)) myUniqueRecordsIndices.Add RC, CStr(RC) SkipRecord: Next RC 'remainder of your code Exit Sub ErrHandler: Resume SkipRecord End Sub In article , "WJ" wrote: Hello, I'm using the technique described in "Microsoft Excel 2000 - Power Programming with VBA" to create a list of unique items out of a total list that might contain duplications. The technique uses the same value being added to the collection as the key for the collection. Thus when a duplicate value is encountered and error will occur. Using "On Error", the attempt to add a duplicate record is ignored and the next record is processed. ----------------------------- On Error GoTo SkipRecord: For RC = 1 To myTotalRecordCount myUniqueRecordsCollection.Add myTotalRecordsCollection(RC), CStr(myTotalRecordsCollection(RC)) myUniqueRecordsIndices.Add RC, CStr(RC) SkipRecord: Next RC ----------------------------- I happen to know that my first, second, and ninth records are unique and the rest are duplicates. The procedure executes fine, adding records 1 and 2 and their indices to the respective collections, skipping record 3 and then failing at RC = 4. Records 3 and 4 are both identical to record 2. The error is the duplicate key error, the exact error I'm trying to ignore as far as I can tell: ----------------------------- Run-time error '457': This key is already associated with an element of this collection ----------------------------- It almost acts as if it has forgotten the command, "On Error GoTo SkipRecord" between RC =3 and RC=4. Does anyone have any advice? Thanks, WJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique items...please help | Excel Worksheet Functions | |||
Sumif only unique items | Excel Discussion (Misc queries) | |||
AutoFilter and unique items | Excel Discussion (Misc queries) | |||
Adding a collection to a wksht | Excel Discussion (Misc queries) | |||
counting unique items | Excel Discussion (Misc queries) |