Checking Fields row by row

[Paul]

Please review the response below which I had recieved from
the MS Project support newsgroup.

Is it possible to program VB to loop through and
incrementally look at each row in a field and compare it
to the previous? I have tried unsuccessfully and is most
likely related to syntax issues. It would increment the
rows randomly, sometimes 5 at a time. If I can compare
the fields for two successive entries, I can then write to
and autofilter a flag to look at duplicates.

Thank you
---------------------------------------------------
Hi Paul,

It does work the way you describe.
But if you want an alternative way, Sort the list for the
field you want to
examine, and compare two successive entries; if equal set
a flag field to
true for both, which then allows you to filter, even after
re-sorting in the
original order.

HTH

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
Project Management Consultancy
Prom+ade BVBA
32-495-300 620

Is it possible to autofilter or filter in order to display
only duplicate items under a field? I would like to be
able to find any possible duplicate entries in the system
at the click of a button.

If not, is it possible to do so within VB? I have
previously tried unsuccessfully to have the program look
at the top cell in a field/column, and then repeatedly
move down to look at the next cell in that column.

Thank you

..

 

[John]

Paul,
If I understand your post correctly, you would like to examine, for
example, the contents of Text1 in the current row and determine if it is
identical to Text1 of the previous row. If this is correct, the
following code should do it. Note: I don't have Project open at the
moment so I didn't actually try this code.

Sub CheckFields()
FirstRow = True
For each t in ActiveProject.Tasks
If Not t is Nothing Then
If FirstRow = False Then
If t.Text1 = Temp Then t.Flag1 = True
End If
Temp = t.Text1
FirstRow = False
End If
Next t
End Sub

John

 

[Jack D.]

Paul,
If I understand your post correctly, you would like to examine, for
example, the contents of Text1 in the current row and determine if it is
identical to Text1 of the previous row. If this is correct, the
following code should do it. Note: I don't have Project open at the
moment so I didn't actually try this code.

Sub CheckFields()
FirstRow = True
For each t in ActiveProject.Tasks
If Not t is Nothing Then
If FirstRow = False Then
If t.Text1 = Temp Then t.Flag1 = True
End If
Temp = t.Text1
FirstRow = False
End If
Next t
End Sub

John

Actually John, he was asking if there was a way to find duplicates. The
suggestion was to sort by that field and then compare with previous task.
Your code will flag the second instance of the task as being a duplicate,
but only if you modify it like this:

Sub Macro1()
'First permanently renumber the tasks
Sort Key1:="Name", Renumber:=True
'then flag any which are duplicates.
FirstRow = True
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If FirstRow = False Then
If t.Name = Temp Then t.Flag1 = True
End If
Temp = t.Name
FirstRow = False
End If
Next t
End Sub

If you want to flag all occurances of a duplicated task you will have to add
another step which will flag the previous task

Sub Macro1()
Dim t As Task
Dim ts As Tasks
'sort by the field you want to detect duplicates in
Sort Key1:="Name", Renumber:=True
Set ts = ActiveProject.Tasks
'loop through all tasks
For Each t In ActiveProject.Tasks
'clear the flag first
t.Flag1 = False
If Not t Is Nothing Then
'skip the very first task
If Not t.ID = 1 Then
'check to see if the name is the same as previous task
If t.Name = ts(t.ID - 1).Name Then 'you can add additional
conditions here.
'if it is, set the flag
t.Flag1 = True
'then set the flag for the previous task
ts(t.ID - 1).Flag1 = True
End If
End If
End If
Next t
End Sub

--
Please try to keep replies in this group. I do check e-mail, but only
infrequently. For Macros and other things check http://masamiki.com/project

-Jack Dahlgren, Project MVP


+++++++++++++++++++