Trevor:

Thanks for the reply... at least I know now that I'm not doing some wrong.
I appreciate your stepping through this process and providing me such
detailed feedback.

I'll look into other ways as to how we can clean the data up.

Again, thanks!!!

Tom


"Trevor Shuttleworth" wrote in message
...
Tom

when the function fails, you are getting Error 2015 from the line:

arr = Evaluate("={""" & WorksheetFunction.Substitute(target, ";", """,""")
&
"""}")

With the sample record you provided, I tried building up the length of
data
character by character and it failed at 226 characters -

"Attend FPC; Attend FPC; Attend FPC; Attend FPC; Attend IPC, MPC; Attend
IPC, MPC; Attend IPC, MPC; Attend IPC, MPC; Attend training workshops;
Collect records and data; Collect records and data; Collect records and
data; Colle"

This produced:

"Attend FPC; Attend IPC, MPC; Attend training workshops; Collect records
and
data; Colle"

which is actually 87 characters long with 4 semicolons (

Moving "along" the data by 222 characters selected:

"Collect records and data; Conduct analysis of ALLIED ACTION 04; Conduct
analysis of ALLIED WARRIOR; Conduct analysis of BLUE GAME 04; Conduct
analysis of DESTINED GLORY; Conduct analysis of exercises, training and
CDE;
Deploy for exercise"

238 characters with 6 semicolons. These elements are all unique and
"dedupe" to the same thing:

"Collect records and data; Conduct analysis of ALLIED ACTION 04; Conduct
analysis of ALLIED WARRIOR; Conduct analysis of BLUE GAME 04; Conduct
analysis of DESTINED GLORY; Conduct analysis of exercises, training and
CDE;
Deploy for exercise"

There is a Microsoft Knowledge Base entry
http://support.microsoft.com/default...b;en-us;211601

In Microsoft Excel 2000, if you use the Evaluate method in a Visual Basic
for Applications macro to evaluate a statement or formula that contains a
date, the method may return either of the following error messages:



Run-time error '13': Type mismatch

-or-

Error 2015



Not exactly your problem but maybe too close for comfort ?



Sorry I can't see a pattern or offer any suggestions. Any other way to
populate the array/collection ?



Regards



Trevor





"Tom Bock" wrote in message
...
After some data cleanup (concatenating cells from different worksheets),
I
often end up with duplicate values.

For instance, a cell may contain "richmond; atlanta; richmond; san
diego;
atlanta; atlanta"

To eliminate the duplication, I execute the "dedupe" function below (its
code is between the ***s) and the cell value is now "richmond; atlanta;
san
diego".

=dedupe(A1)

*************************************************
Function dedupe(target As String)
Dim nodupes As New Collection
Dim arr As Variant
arr = Evaluate("={""" & WorksheetFunction.Substitute(target, ";",
""",""")
& """}")
On Error Resume Next
For Each ar In arr
nodupes.Add Item:=Trim(ar), key:=CStr(Trim(ar))
Next ar
On Error GoTo 0
first = True
For Each ar In nodupes
If first Then
dedupe = ar
first = False
Else
dedupe = dedupe & "; " & ar
End If
Next ar
End Function
*************************************************


So far, this function is working fine; however, we're now doing cleanup
on
some "real data". The text in the
cells can be fairly extensive (eventually will be transferred into a
database).

At this time, the "working function" does not work any longer.... I have
no
idea why? Below is an example of a
lengthy cell content.

Does anyone know why it wouldn't work now??? Actually, the function
does
not give any text (cell is now completely empty)

Thanks!

Tom


Actual Data Example:
====================

Attend FPC; Attend FPC; Attend FPC; Attend FPC; Attend IPC, MPC; Attend
IPC,
MPC; Attend IPC, MPC; Attend IPC, MPC; Attend training workshops;
Collect
records and data; Collect records and data; Collect records and data;
Collect records and data; Conduct analysis of ALLIED ACTION 04; Conduct
analysis of ALLIED WARRIOR; Conduct analysis of BLUE GAME 04; Conduct
analysis of DESTINED GLORY; Conduct analysis of exercises, training and
CDE;
Deploy for exercise; Deploy for exercise; Deploy for exercise; Deploy
for
exercise; Develop Analysis Plan; Develop Analysis Plan; Develop Analysis
Plan; Develop Analysis Plan; Produce EXPI/EXOPORD inputs; Produce
EXPI/EXOPORD inputs; Produce EXPI/EXOPORD inputs; Produce EXPI/EXOPORD
inputs; Publish analysis report; Publish analysis report; Publish
analysis
report; Publish analysis report; Reconstruct events; Reconstruct events;
Reconstruct events; Reconstruct events; Report to the OSE and to the
Lessons
Learned Division and Remedial Action Process; Report to the OSE and to
the
Lessons Learned Division and Remedial Action Process; Report to the OSE
and
to the Lessons Learned Division and Remedial Action Process; Report to
the
OSE and to the Lessons Learned Division and Remedial Action Process