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?