W
WJ
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
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