I'm running Office 2003 in Windows XP. In Excel, I have a a set of macros
that run on a file to parse out the data into a format I can use for
creating mail-merge letters in Word. At one point in the process I have a
worksheet that looks similar to this:

DistrictName LetterID
Azure Hills C08-0300 Jan
Babylon C08-0301 Mar
Babylon C08-0301 Mar
Corners C08-0302 Mar
Corners C08-0302 May
Corners C08-0302 May

Essentially the first column is the DistrictName, the 2nd column is the
LetterID, the 3rd column is a month and the 4th column is where a final
value for a filename will be written.

I have a loop that grabs the values in A1 and B1 (DistrictName and
LetterId), assigns them to the variables DistrictName and LetterID and then
sends them to a Function entitled DynFilename. When the function returns a
value, that value is written into the 4th column and then the focus is moved
down one row and we loop. My problem occurs within the function DynFilename.

(If all of the above is extraneous information, I apologize. I just want to
make sure I provide enough information so you can understand what I am
trying to do.)

The function DynFilename looks like this:

Function DynFilename(DistrictName, LetterId) As String

Dim Rng As Range
Dim cUnique As Collection
Dim cell As Range
Dim sh As Worksheet
Dim vNum As Variant

Set sh = ActiveSheet
Set Rng = sh.Range("A2", sh.Range("A2").End(xlDown))
Set cUnique = 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
On Error Resume Next
For Each cell In Rng.Cells
If cell.Value = DistrictName And cell.Offset(0, 1).Value =
LetterId Then
cUnique.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 vNum In cUnique
DynFilename = DynFilename & vNum
Next vNum

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

End Function

Essentially I'm running this function so I can read in all the months that
are associated with a given district and letterid and then uniquely identify
those months to append to the filename. So if there were 4 entries for a
district and they had months "Jan, Mar, Mar, Mar", then I only want to show
"JanMar" when I generate to final filename.

When I run this, I get the following error:
Run-time error '457'
This key is already associated with an element of this collection

In stepping through the code, it appears that everything works fine for the
first loop through. The error appears when trying to process the second row
of data.

Admittedly, I haven't used this particular code in months...but it was
working fine when I ran it back in January. How do I get this code working
again?

-Brian

Apparently replacing "On Error GoTo 0" with "On Error Resume Next" got rid
of the error.

-Brian

"Brian Beck" wrote in message ...
I'm running Office 2003 in Windows XP. In Excel, I have a a set of macros
that run on a file to parse out the data into a format I can use for
creating mail-merge letters in Word. At one point in the process I have a
worksheet that looks similar to this:

DistrictName LetterID
Azure Hills C08-0300 Jan
Babylon C08-0301 Mar
Babylon C08-0301 Mar
Corners C08-0302 Mar
Corners C08-0302 May
Corners C08-0302 May

Essentially the first column is the DistrictName, the 2nd column is the
LetterID, the 3rd column is a month and the 4th column is where a final
value for a filename will be written.

I have a loop that grabs the values in A1 and B1 (DistrictName and
LetterId), assigns them to the variables DistrictName and LetterID and
then sends them to a Function entitled DynFilename. When the function
returns a value, that value is written into the 4th column and then the
focus is moved down one row and we loop. My problem occurs within the
function DynFilename.

(If all of the above is extraneous information, I apologize. I just want
to make sure I provide enough information so you can understand what I am
trying to do.)

The function DynFilename looks like this:

Function DynFilename(DistrictName, LetterId) As String

Dim Rng As Range
Dim cUnique As Collection
Dim cell As Range
Dim sh As Worksheet
Dim vNum As Variant

Set sh = ActiveSheet
Set Rng = sh.Range("A2", sh.Range("A2").End(xlDown))
Set cUnique = 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
On Error Resume Next
For Each cell In Rng.Cells
If cell.Value = DistrictName And cell.Offset(0, 1).Value =
LetterId Then
cUnique.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 vNum In cUnique
DynFilename = DynFilename & vNum
Next vNum

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

End Function

Essentially I'm running this function so I can read in all the months that
are associated with a given district and letterid and then uniquely
identify those months to append to the filename. So if there were 4
entries for a district and they had months "Jan, Mar, Mar, Mar", then I
only want to show "JanMar" when I generate to final filename.

When I run this, I get the following error:
Run-time error '457'
This key is already associated with an element of this collection

In stepping through the code, it appears that everything works fine for
the first loop through. The error appears when trying to process the
second row of data.

Admittedly, I haven't used this particular code in months...but it was
working fine when I ran it back in January. How do I get this code working
again?

-Brian

I think you had the "on error resume next in the wrong spot:


For Each cell In Rng.Cells
If cell.Value = DistrictName _
And cell.Offset(0, 1).Value = LetterId Then
On Error Resume Next
cUnique.Add Format(cell.Offset(0, 2).Value, "mmm"), _
Format(cell.Offset(0, 2).Value, "mmm")
On Error GoTo 0
End If
Next cell

or you had the "on error goto 0 in the wrong spot:

On Error Resume Next
For Each cell In Rng.Cells
If cell.Value = DistrictName _
And cell.Offset(0, 1).Value = LetterId Then
cUnique.Add Format(cell.Offset(0, 2).Value, "mmm"), _
Format(cell.Offset(0, 2).Value, "mmm")
End If
Next cell
On Error goto 0

In either case, you want to ignore any error caused by trying to add a duplicate
item to the collection.



Brian Beck wrote:

Apparently replacing "On Error GoTo 0" with "On Error Resume Next" got rid
of the error.

-Brian

"Brian Beck" wrote in message ...
I'm running Office 2003 in Windows XP. In Excel, I have a a set of macros
that run on a file to parse out the data into a format I can use for
creating mail-merge letters in Word. At one point in the process I have a
worksheet that looks similar to this:

DistrictName LetterID
Azure Hills C08-0300 Jan
Babylon C08-0301 Mar
Babylon C08-0301 Mar
Corners C08-0302 Mar
Corners C08-0302 May
Corners C08-0302 May

Essentially the first column is the DistrictName, the 2nd column is the
LetterID, the 3rd column is a month and the 4th column is where a final
value for a filename will be written.

I have a loop that grabs the values in A1 and B1 (DistrictName and
LetterId), assigns them to the variables DistrictName and LetterID and
then sends them to a Function entitled DynFilename. When the function
returns a value, that value is written into the 4th column and then the
focus is moved down one row and we loop. My problem occurs within the
function DynFilename.

(If all of the above is extraneous information, I apologize. I just want
to make sure I provide enough information so you can understand what I am
trying to do.)

The function DynFilename looks like this:

Function DynFilename(DistrictName, LetterId) As String

Dim Rng As Range
Dim cUnique As Collection
Dim cell As Range
Dim sh As Worksheet
Dim vNum As Variant

Set sh = ActiveSheet
Set Rng = sh.Range("A2", sh.Range("A2").End(xlDown))
Set cUnique = 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
On Error Resume Next
For Each cell In Rng.Cells
If cell.Value = DistrictName And cell.Offset(0, 1).Value =
LetterId Then
cUnique.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 vNum In cUnique
DynFilename = DynFilename & vNum
Next vNum

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

End Function

Essentially I'm running this function so I can read in all the months that
are associated with a given district and letterid and then uniquely
identify those months to append to the filename. So if there were 4
entries for a district and they had months "Jan, Mar, Mar, Mar", then I
only want to show "JanMar" when I generate to final filename.

When I run this, I get the following error:
Run-time error '457'
This key is already associated with an element of this collection

In stepping through the code, it appears that everything works fine for
the first loop through. The error appears when trying to process the
second row of data.

Admittedly, I haven't used this particular code in months...but it was
working fine when I ran it back in January. How do I get this code working
again?

-Brian


--

Dave Peterson