vba that codes 1 to a list of task, and 2 to another list

[Hung Le]

Is it possible to write vba that codes 1 to a list of
task, and 2 to another list, so that it can be grouped
into two difference groups?
I have new list of tasks to be grouped every week and tire
of manually group them. Could some one shows me an example
or walk me thru? Thanks
Hung Le

 

[JackD]

Sure. What is the difference between the two groups of tasks?
If you can write code to find the difference then this is easy.
But before you write code do this:

1) First pick a field that you are going to group by. In this example we can
use Text25.
2) Create a group on Text25 (in this example we call it myGroup)
3) Save project

Now you write some code.

Sub lalala()
Dim t As Task
Dim ts As Tasks
Set ts = ActiveProject.Tasks
For Each t In ts
If Not t Is Nothing Then
If t.Text25 = "" Then
'write what ever condition you want to test here
If t.Name = "foo" Then
t.Text25 = "Group1"
Else: t.Text25 = "Group2"
End If
End If
End If
Next t
GroupApply Name="myGroup"
End Sub

The hard part is writing the statement that determines which tasks go in
group1 and which go in group2.
You have to figure that out unless you can tell me how they are different.

-Jack

 

[Hung Le]

The first group is those tasks starts or finishes this
week, and the second group is those tasks in process
(between 1% to 99%)
Regards

 

[JackD]

That will be difficult as there can be a task which is BOTH in progress and
which finishes this week.
How do you decide which group it goes in?

-Jack

 

[Hung Le]

In that case, It has been grouped under group 1, which
finishes this week.
Regards,

 

[JackD]

This has been updated to flag the Group1 tasks. There is still a question
about what to do with the tasks that don't fit either condition (not
starting or finishing and not in progress). With this code they are in
group2.

Sub lalala()
Dim t As Task
Dim ts As Tasks
Set ts = ActiveProject.Tasks
For Each t In ts
If Not t Is Nothing Then
'here we check if it has a start or finish in this week
If ((t.Start > ActiveProject.StatusDate) And (t.Start <
(ActiveProject.StatusDate + 7))) _
Or ((t.Finish > ActiveProject.StatusDate) And (t.Finish <
(ActiveProject.StatusDate + 7))) Then
t.Text25 = "Group1"
Else: t.Text25 = "Group2"
End If
End If
Next t
GroupApply Name = "myGroup"
End Sub

 

[Hung Le]

Below is the code. Please let me know what is wrong?
Thanks



Sub lalala()
Dim t As Task
Dim ts As Tasks
Set ts = ActiveProject.Tasks
For Each t In ts
If Not t Is Nothing Then
'here we check if it has a start or finish in this
week
If ((PercentComplete > 0) And (PercentComplete <
100)) Then
t.Text25 = "Group2"
If ((t.Start > ActiveProject.StatusDate) And
(t.Start < (ActiveProject.StatusDate + 7))) _
Or ((t.Finish > ActiveProject.StatusDate) And
(t.Finish < (ActiveProject.StatusDate + 7))) Then
t.Text25 = "Group1"
If ((t.Start > ActiveProject.StatusDate + 8) And
(t.Start < (ActiveProject.StatusDate + 14))) _
Or ((t.Finish > ActiveProject.StatusDate + 8) And
(t.Finish < (ActiveProject.StatusDate + 14))) Then
t.Text25 = "Group3"
If ((t.Start > ActiveProject.StatusDate + 15) And
(t.Start < (ActiveProject.StatusDate + 35))) _
Or ((t.Finish > ActiveProject.StatusDate + 15) And
(t.Finish < (ActiveProject.StatusDate + 35))) Then
t.Text25 = "Group4"
Else: t.Text25 = "Group5"
End If
End If
Next t
GroupApply Name = "myGroup"
End Sub

 

[JackD]

Hung,

I don't know exactly what you want it to do, but it looks like your
if...then statements are not correct.
Should be

if (something) then
'do stuff
end if

The way you have it constructed they are nested. Nothing will happen unless
the first condition is true.
Also, all of the first line of the if...then statement should be on the same
line. You can break it up into multiple lines using the line continuation
character _ but you have not done this or perhaps your news poster has
broken the lines.

Just guessing what you are after I'd make the following changes. I think I'd
try and clean it up some more.

You know that you need to go to the project menu/project information dialog
box and set the status date for your project first right?

Sub lalala()
Dim t As Task
Dim ts As Tasks
Dim sDate as date
Set ts = ActiveProject.Tasks
sDate = activeproject.statusdate
For Each t In ts

If Not t Is Nothing Then
If ((PercentComplete > 0) And (PercentComplete < 100)) Then
t.Text25 = "Group2"
End if

If ((t.Start > sDate) And _
(t.Start < (sDate+ 7))) _
Or ((t.Finish > sDate) And _
(t.Finish < (sDate+ 7))) Then
t.Text25 = "Group1"
End if

If ((t.Start > sDate+ 8) And _
(t.Start < (sDate+ 14))) _
Or ((t.Finish > sDate+ 8) And _
(t.Finish < (sDate+ 14))) Then
t.Text25 = "Group3"
End if

If ((t.Start > sDate+ 15) And _
(t.Start < (sDate+ 35))) _
Or ((t.Finish > sDate+ 15) And _
(t.Finish < (sDate+ 35))) Then
t.Text25 = "Group4"

if t.Text25 = "" then
t.text25 = "Group5"
end if

Next t
GroupApply Name = "myGroup"
End Sub

 

[Hung Le]

Thanks very much for your help Jack. It is beautiful. I am
glad that we have you Jack.
Hung