Filter Results count

[Doug]

I am filtering resources for tasks within a date range
with a macro in the middle of a DoWhile Loop. Is there a
way to capture the number of tasks returned after
filtering? Sometimes the results are no tasks and my macro
crashes. When there are results it would be useful to know
how many tasks remain.
TIA DP

 

[Gérard DUCOURET]

Hello Doug
Try this :
Sub NbrTachesSelection()
FilterApply Name:="MyFilterOnFlag" 'You have to create the filter before...
SelectSheet
MsgBox "Number of selected tasks : " & ActiveSelection.Tasks.Count, vbOKOnly,
"ActiveSelection.Tasks.Count"
End Sub

Hope this help,

Gérard Ducouret

 

[Gérard DUCOURET]

Doug,
If you are working in the Resource Usage view, it would be easier to check all tasks whose
assignments comply with your criteria, and to count them each time you find one.
Here under is an exerp of a procedure whith do something like that.

For Each oTache In ActiveProject.Tasks
If Not (ActiveCell.Task Is Nothing) Then
If oTache.Summary = False Then
i = 0 'Initialisation
TotEC = 0
TotAC = 0
TotPR = 0
For Each Attrib In oTache.Assignments
EC = 0
AC = 0
PR = 0
i = i + 1
y = y + 1
If ActiveProject.Resources(Attrib.ResourceID).Group = "Equipe centrale" Then
EC = Attrib.Cost
TotEC = TotEC + EC
oTache.Assignments(i).Cost1 = EC
oTache.Cost1 = TotEC
................

"Doug" <[email protected]> a écrit dans le message de [email protected]...
Gerard, thanks for the quick response. But...if I am in a
resource usage view I can't ask for
the "ActiveSelection.Tasks.Count" only
the "ActiveSelection.Resources.Count" And, if no
resources are left after filtering I get the same error:
"object required" in both cases

below is my code:
'Note: Must be run from the resource usage view
'filter is created first, this part works, then applied

Sub aaaaafilter()
' Macro aafilter

FilterApply Name:="Using Resource and Date Range"

SelectSheet
MsgBox ActiveSelection.Resources.Count
'ActiveSelection.Tasks.Count
MsgBox ActiveSelection.Tasks.Count
End Sub

-----Original Message-----
Hello Doug
Try this :
Sub NbrTachesSelection()
FilterApply Name:="MyFilterOnFlag" 'You have to create the filter before...
SelectSheet
MsgBox "Number of selected tasks : " &

ActiveSelection.Tasks.Count, vbOKOnly,

 

[Jan De Messemaeker]

Hi Doug,

I lokoked this up a while ago and by far the easiest way is to preceed the
Activeselection.resources.count by an On Error statement which allows you to
capture the error.

HTH

Jan De Messemaeker, Project MVP
Gerard, thanks for the quick response. But...if I am in a
resource usage view I can't ask for
the "ActiveSelection.Tasks.Count" only
the "ActiveSelection.Resources.Count" And, if no
resources are left after filtering I get the same error:
"object required" in both cases

below is my code:
'Note: Must be run from the resource usage view
'filter is created first, this part works, then applied

Sub aaaaafilter()
' Macro aafilter

FilterApply Name:="Using Resource and Date Range"

SelectSheet
MsgBox ActiveSelection.Resources.Count
'ActiveSelection.Tasks.Count
MsgBox ActiveSelection.Tasks.Count
End Sub

-----Original Message-----
Hello Doug
Try this :
Sub NbrTachesSelection()
FilterApply Name:="MyFilterOnFlag" 'You have to create the filter before...
SelectSheet
MsgBox "Number of selected tasks : " &

ActiveSelection.Tasks.Count, vbOKOnly,

 

[Doug]

Jan:
I have tried that, great minds must think alike. Here's
the problem I have. It works great when tested all by
itself. I trap the error, jump around the problem and
carry on! But, when I put it into the larger macro it does
not work. Now, it may be due to being inside a Do While
Loop in the larger macro. The jump is to the bottom of the
Loop, but still inside it. Below the error in question I
pop out to XL twice for some copying and pasting. This all
works fine but when I introduce the code around filtering
and jumping past the rest of the code it errors out on me.
Even with the On Error Jump To... preceeding the offending
line. I can send you the entire code if you'd like (I'll
strip out all the misc. stuff so it will read faster)
Please let me know if you'd like to assist with this
further.
Thanks!

 

[Jack D.]

Did you try on error resume?

Jan:
I have tried that, great minds must think alike. Here's
the problem I have. It works great when tested all by
itself. I trap the error, jump around the problem and
carry on! But, when I put it into the larger macro it does
not work. Now, it may be due to being inside a Do While
Loop in the larger macro. The jump is to the bottom of the
Loop, but still inside it. Below the error in question I
pop out to XL twice for some copying and pasting. This all
works fine but when I introduce the code around filtering
and jumping past the rest of the code it errors out on me.
Even with the On Error Jump To... preceeding the offending
line. I can send you the entire code if you'd like (I'll
strip out all the misc. stuff so it will read faster)
Please let me know if you'd like to assist with this
further.
Thanks!

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

 

[Doug]

Yep...
I seem to be having luck with
===========================
If (ActiveCell.Resource Is Nothing) Then 'No Resources
found
MsgBox "No Resources Found"
GoTo pJumpOut
Else 'Some
Resources found
MsgBox ActiveSelection.Resources.Count & " Resource
(s) Found"
End If 'Resources
found ??
===================
Now I just have to try that with the whole macro, but
that's another day! Still can't count the # of rows
remaining after filtering tho.
Thanks,
Doug

 

[Jan De Messemaeker]

Hi Doug,
This case seems complex enough to be handled without filtering.
Loop through all resources,
verify whether the resource matches the criteria,
if yes loop through the resource's assignments,
verify whether it meets yoyur criteria
and add one to your total.
HTH

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