Deleting duplicate resource assignments

[JD]

Hey guys, I have another question for you. I am using this macro to
automatically assign resources to tasks, based on a number placed in the
field Number1.

Sub Automatic_Resource_Assignment()

Dim Temp1 As Long
Dim Temp2 As Long

On Error Resume Next

For Temp1 = 1 To ActiveProject.Resources.Count

For Temp2 = 1 To ActiveProject.Tasks.Count

If ActiveProject.Resources(Temp1).Number1 =
ActiveProject.Tasks(Temp2).Number1 Then
ActiveProject.Tasks(Temp2).Assignments.Add ResourceID:=Temp1

Next Temp2

Next Temp1

End Sub

It works perfectly, but it sometimes assigns a resource to a task three or
four times. This isn't a problem with the macro, it's an issue with the
data. I'm wondering if there is a way to prevent it from assigning a
resource to the same task twice, or a way to write a macro that would be run
afterward and delete the duplicates.

 

[Jan De Messemaeker]

Hi,

Are you sure?
You can't assign the same resource more than once to a task, that generates
an error message.
Don't you have multiple resource with the same name?

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[JD]

Yes, I do have multiple resources with the same name. Actually, there are
about 500 resources, with about 70 names shared between them. This is just
the way the database is set up. I know we aren't really using Project like
its meant to, its just being used as part of a larger report. Anyways, do
you have any ideas how to delete the duplicates in the gantt chart view? I
can go in and edit it by hand, so I'm guessing a macro could do it too, but I
don't know where to start.

 

[Jan De Messemaeker]

Hi,

There are no true "duplicates" just different resources with the same name!
To avoid this before assignments.add add an if such as
if
instr(activeproject.tasks(Temp2).resourcenames,activeproject.resources(temp1).name)=0
then

i..e only add a resource with a name that does not yet appear in
ResourceNames
HTH
--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf