MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Duplicates in a collection

  Asked By: Tracey    Date: Sep 18    Category: MS Office    Views: 1249

When running a macro in Excel 2003 that I hadn't used in months, I
suddenly encountered an error that have never occurred before. When I
run the function below, I get:

run-time error 457: This key is already associated with an element of
this collection

The function is as follows:

Function DynFilename(DistrictName, LetterId) As String

Dim cell As Range
Dim colMonths As Collection
Dim itm

'Create a new collection and assign it to colMonths
Set colMonths = New Collection

'Assign a value to DynFilename
DynFilename = DistrictName & "_" & LetterId & "_XX_"

'This loops through cells in the worksheet and checks to see if the value
'in the currently selected cell is equal to the value in the variable
'DistrictName AND whether the value in the cell directly to the right
'of the currently selected cell is equal to the value in the variable
'LetterId. If it is, then the value in the cell 2 to the right of the
'currently selected cell is placed into the collection colMonths
For Each cell In ActiveSheet.Range("A:A")
If cell.Value = DistrictName And cell.Offset(0, 1).Value =
LetterId Then
On Error Resume Next
colMonths.Add Format(cell.Offset(0, 2).Value, "mmm"), _
Format(cell.Offset(0, 2).Value, "mmm")
On Error GoTo 0
End If
Next cell

'The value in DynFilename is appended with the values that
'are in colMonths
For Each itm In colMonths
DynFilename = DynFilename & itm
Next itm

'Append "08" to the end of DynFilename
DynFilename = DynFilename & "08"

End Function

Now my understanding of this process is that I should expect to get an
error, because I am adding a duplicate element...however, isn't the
"On Error Resume Next" statement supposed to make the code simply move
on to the next cell?

Let me give an example. Say I have:

DistrictName LetterID
AAAAA C08-0101 Feb
BBBBB C08-0102 Oct
BBBBB C08-0102 Oct

When the script runs through the first row (after the header), it
correctly returns the following:

DistrictName LetterID
AAAAA C08-0101 Feb AAAAA_C08-0101_XX_Feb08
BBBBB C08-0102 Oct
BBBBB C08-0102 Oct

The script then processes the next row with no problem and moves onto
the next row because the If condition continues to be true. It is at
this point that it encounters the error and stops. Ultimately I want
it to ignore the error and just move on.

What am I missing here?



No Answers Found. Be the First, To Post Answer.

Didn't find what you were looking for? Find more on Duplicates in a collection Or get search suggestion and latest updates.