Project VBA anyone?? 1

[KenWright]

2 Cols of data, Col 1 being Taskname, Col 2 being Duration

Would like a routine to start at first Taskname, look at Duration, and if duration is 0.2 then Taskname becomes

Taskname (WBS Placeholder)

ie, just tack a space and (WBS Placeholder) onto the existing taskname.

Number of tasks random so looking for some kind of loop, or For Next etc, but the object model just looks alien to me :-(

Many thanks
Ken...................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Sorry, just realise I never said explicitly that the loop is to look at each duration and every time it encounters a duration of 0.2, then the Taskname gets edited in line with the OP, ie


Taskname ==> Taskname (WBS Placeholder)

Cheers
Ken..............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

Ken - I'm assuming excel.....further assuming that you have headers and that the columns are A & B

lRow = cells(65536,1).end(xlup).row
for each c in range("A1:A" & lRow)
if c.offset(0,1).value = 0.2 then
 c.value = c.value & " (WBS Placeholder)
End If
Next

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

LOL - Geoff - I know you didn't think I'd come on and pitch a question like that in Excel

I was hoping the title of the question would do it for me, but if not then it's MS Project that this is in.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Ok I got it, one of the folks in the MS groups posted an answer for me

Have to say though, it looks NOTHING like the recorded code I got in project, but looks very much like normal VBA. Grrr - Damn Macro recorder.

sub ken()
for each task in activeproject.tasks
if not task is nothing then
    if task.duration = .2*480 then
        task.name = task.name & " WBS Placeholder"
    end if
end if
next task
end sub

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[TonyJollans]

Hi Ken,

I haven't done very much VBA in Project but two things:

1. Why the check on Task being Nothing inside the For .. Next? By definition it is something isn't it?

2. Task durations are in minutes so the 480 in your check is for an 8 hour day. Project lets you tie yourself in knots but you should probably adjust according to the number of hours in the day, something like ..

[blue]If Task.Duration = .2 * ActiveProject.HoursPerDay * 60 Then ...[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

LOL Ken !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Tony, cheers for that as you got me curious. I asked the question and got back the the two following answers from different people:-

'A completely blank line gives a task which is a Nothing object Since that has no properties it would give a Run Time Error.'

'This is a common problem when Excel people start using project. They often use blank lines to format their project.
For most project VBA it is better to work on a task collection rather than going through the cells in the "table" so while you could do it similar to
excel, it is better not to.'

Cheers for the heads up on the hours bit though as he said that would be useful to him, so star from me

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[TonyJollans]

Thanks for that Ken. I just tried it and it does indeed return Nothing (I did say I hadn't done much VBA in Project, didn't I? [LOL]).

I'm not sure about Excel people using blank lines. I don't think good Excel people would, but what would Project people know?

Thanks for the star.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[KenWright]

rotflmao - I was very tempted to make the same reply, but I bit my tongue seeing as they were helping me

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------