Delete PayRates

[RC]

Hello, can you advise on what is wrong with this? I am trying to
delete all payrates from Cost Rate Table B. I have 3 payrates to
delete.

Sub DeleteCRT_B()

Dim R As Resource
Dim CRT As CostRateTable
Dim PR As PayRate

For Each R In ActiveProject.Resources
Set CRT = R.CostRateTables("B")
If Not R Is Nothing Then
CRT.PayRates(1).Delete
CRT.PayRates(2).Delete
CRT.PayRates(3).Delete
End If
Next R

MsgBox "Complete"

End Sub

 

[Jim Aksel]

Two things. I had trouble using the For Each R in ActiveProject.Resources.
Instead, I had better luck with iterating each integer.
For i = 1 to ActiveProjectResources.Count

Also, I don't see .Delete being a valid member of PayRates. From the Object
Browser valid methods are: Add, Application, Count, Item, Parent. Perhaps:

ActiveProject.Resources(r.Name).CostRateTables("B").PayRates.Item(1) = "0"
(or =Nothing)

You can also loop through that
While ActiveProject.Rsources(R.Name).CostRateTables("B").PayRates.Count >0

but be careful because every time you delete one, the .Count property
decreases.

My project was to add additional Payrates into the selected Cost Rate Table,
so my problem was the opposite of yours. Sorry I don't have time to get into
it deeper.

Let us know how things work out.


--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

 

[John]

Hello, can you advise on what is wrong with this? I am trying to
delete all payrates from Cost Rate Table B. I have 3 payrates to
delete.

Sub DeleteCRT_B()

Dim R As Resource
Dim CRT As CostRateTable
Dim PR As PayRate

For Each R In ActiveProject.Resources
Set CRT = R.CostRateTables("B")
If Not R Is Nothing Then
CRT.PayRates(1).Delete
CRT.PayRates(2).Delete
CRT.PayRates(3).Delete
End If
Next R

MsgBox "Complete"

End Sub

RC,
A few years ago I wrote a macro that escalated the pay rates. Part of
that process involved deleting pay rates prior to populating with the
new data. Here is the macro I used for the latter.

Sub PurgeRateTables()
Dim ans As Variant

ans = MsgBox(" C A U T I O N !" & Chr(13) &
Chr(13) & _
"This procedure will completely purge all pay rate" & Chr(13) & _
"information including, Std. Rate, Ovt. Rate, and" & Chr(13) & _
"Cost/Use from pay rate tables in this file" & Chr(13) & Chr(13) & _
"Proceed?", vbYesNo + vbExclamation + vbDefaultButton2, "Resource
Rate Escalator Utility")
If ans = vbNo Then Exit Sub
For Each r In activeproject.Resources
If Not r Is Nothing Then
For i = 1 To 5
Set pr = r.CostRateTables(i).PayRates
pr(1).StandardRate = 0
pr(1).OvertimeRate = 0
pr(1).CostPerUse = 0
If pr.count > 1 Then
For j = pr.count To 2 Step -1
pr(j).Delete
Next j
End If
Next i
End If
Next r
MsgBox "Pay rate information has been purged from this file", vbOKOnly,
"Resource Rate Escalator Utility"
End Sub

Hope this helps.
John
Project MVP

 

[RC]

Thank you both very much for imparting your knowledge! I was able to
make the macro work. (I am new to using VBA with Project and this
newsgroup has been one of my very valuable teachers - I typically try
as many methods suggested in posts that are applicable.)

I also should have explained the full purpose of deleting the
payrates. It is part of a macro similar to John's in that I need to
purge old rates before updating to the new. Thank you again!

RC

 

[John]

Thank you both very much for imparting your knowledge! I was able to
make the macro work. (I am new to using VBA with Project and this
newsgroup has been one of my very valuable teachers - I typically try
as many methods suggested in posts that are applicable.)

I also should have explained the full purpose of deleting the
payrates. It is part of a macro similar to John's in that I need to
purge old rates before updating to the new. Thank you again!

RC

RC,
You are very welcome and thanks for the feedback. Learning VBA can open
up a whole new environment and I applaud you for wanting to learn. For
your reference, fellow MVP, Rod Gill has a book out dealing exclusively
with Project VBA. You can find out more about it at,
http://www.projectvbabook.com.

I basically wrote the little macro I posted to help test a larger macro
I wrote to escalate rates on a yearly basis for a client. It sounds very
similar to what you are trying to do. The macro wasn't as
straightforward as I originally thought it would be. Unfortunately I
found a few things in the Project object browser that weren't quite as
advertised so I had to develop workarounds. But nonetheless, I did get
the desired result and most important, it does what the client needs.

Good luck with your endeavor.

John
Project MVP

 

[RC]

RC,
You are very welcome and thanks for the feedback. Learning VBA can open
up a whole new environment and I applaud you for wanting to learn. For
your reference, fellow MVP, Rod Gill has a book out dealing exclusively
with Project VBA. You can find out more about it at,http://www.projectvbabook.com.

I basically wrote the little macro I posted to help test a larger macro
I wrote to escalate rates on a yearly basis for a client. It sounds very
similar to what you are trying to do. The macro wasn't as
straightforward as I originally thought it would be. Unfortunately I
found a few things in the Project object browser that weren't quite as
advertised so I had to develop workarounds. But nonetheless, I did get
the desired result and most important, it does what the client needs.

Good luck with your endeavor.

John
Project MVP

Hi John,

I have picked up Rod Gill's book! Unfortunately, I have not sat
through and read it. I have learned mostly from the sample code that I
downloaded from his site and chapters here and there that are most
relevant to what I am doing now. I intend to read it cover to cover
one of these days...

RC

 

[John]

Hi John,

I have picked up Rod Gill's book! Unfortunately, I have not sat
through and read it. I have learned mostly from the sample code that I
downloaded from his site and chapters here and there that are most
relevant to what I am doing now. I intend to read it cover to cover
one of these days...

RC

RC,
It's an excellent book for learning Project VBA. It doesn't go into
detail like you will need for your rate update, (covering that level of
detail would require a book many times the size of Rod's book), but it
will give you a solid understanding that will allow you to develop the
detail. And then, there's always this newsgroup if you get stuck.

John