ActiveCell Question in MS Project 1

[jakesups]

I have a column TEXT9 which has values L, D, H, S, or T. I want a routine that will go through all of the valid tasks and give me a total of each. The routine I have doesn't work, but I hope I'm on the right track. Anyone know how to do something like this? The step in the For Each doesn't appear to be talking the proper terminology. Also, how do I set it to start at row 1?
Thanks, Jim

Dim TaskType(5) As Integer
For i = 0 To 5
TaskType(i) = 0
Next i
For Each A In ActiveCell.Task.Text9
Select Case ActiveCell.Task.Text5
Case "D"
TaskType(1) = TaskType(1) + 1
Case "H"
TaskType(2) = TaskType(2) + 1
Case "L"
TaskType(3) = TaskType(3) + 1
Case "S"
TaskType(4) = TaskType(4) + 1
Case "T"
TaskType(5) = TaskType(5) + 1
Case Else
TaskType(0) = TaskType(0) + 1
End Select
Next

 

[SkipVought]

Hi,

I'm guessing here a bit cuz I don't have MS Project, but VBA is pretty consistant.

1. don't believe ActiveCell.Task.Text9 is a collection

    For i = 1 to len(ActiveCell.Task.Text9)
        b = mid(ActiveCell.Task.Text9, i, 1)
        Select Case b
            Case "D"
                TaskType(1) = TaskType(1) + 1
            Case "H"
                TaskType(2) = TaskType(2) + 1
            Case "L"
                TaskType(3) = TaskType(3) + 1
            Case "S"
                TaskType(4) = TaskType(4) + 1
            Case "T"
                TaskType(5) = TaskType(5) + 1
            Case Else
                TaskType(0) = TaskType(0) + 1
        End Select
    Next

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SBendBuckeye]

I belive MSProject exposes a Task collection. I think it also works like Excel in that you can treat it like a spreadsheet. Try something like the following and see if it works. If it doesn't post again and I'll try to go over to our lab at work which has MSProject loaded and see if I can help you. Let's assume Text9 is Col 5 for our purposes.

For Each Task In ActiveProject.Tasks
lngTaskCtr = lngTaskCtr + 1 'Count the rows
strText9 = UCase$(Task.Cells(lngTaskCtr, 5).Value)
Select Case strText9
End Select
Next Task

I haven't done anything with MSProject for about 3 months so if the above isn't close, please accept my apologies. I am pretty certain about the Tasks part however. If you can get this much to work, then view the local variables in debug mode and poke around to see what you can find out.

For Each Task In ActiveProject.Tasks
Next Task

One big gotcha' to avoid - The TaskID is relative to the row in your project, as the user inserts or deletes Tasks the TaskID will adjust accordingly just like the row number does in Excel when you insert or delete rows.

I don't remember exactly what it was called, but there is a second ID called PermanentID or something like that which does not change. It works similarly to the way CodeName works with Excel worksheets, it is not affected by changes the user makes. You can calculate it because when it is created it is equal to the ID which corresponds to the task row number when it was created.

I hope this made a least a little bit of sense. Good Luck!


Have a great day!

j2consulting@yahoo.com

 

[jakesups]

Thanks for your help. I hope you all had a nice holiday. After working with this for the last half hour, this is what I came up with to access information in a column of data from MS Project. This works, and now I can use it for more reporting on the other data in my project.
Thanks again, Jim

For Each Task In ActiveProject.Tasks
Temp = Trim(Task.Text9) 'A smart idea would be to use UCASE also
Select Case Temp
Case "D"
TaskType(1) = TaskType(1) + 1
Case "H"
TaskType(2) = TaskType(2) + 1
Case "L"
TaskType(3) = TaskType(3) + 1
Case "S"
TaskType(4) = TaskType(4) + 1
Case "T"
TaskType(5) = TaskType(5) + 1
Case Else
TaskType(0) = TaskType(0) + 1
End Select
Next