Delete row from Cost Rate Table

J

John4bank

In project I have developed a macro that burdens a base salary rate with
different overhead rates. My problem is that if someone had previously
entered a rate to take effect on the same day as the new burdens take effect
I cannot overide the old rate.

Is there a way to cycle through all of the resources on the resource sheet
and delete all rate changes on ____ date or is there a way to simply replace
them as the macro cycles through?

My current code looks like:
FringeNum = Val(SFringe)
CRate =
Rsr.CostRateTables("A").PayRates.Item("1").StandardRate
CRate = Replace(CRate, "$", "")
CRate = Replace(CRate, "/h", "")
MValue = Val(CRate)
aSNum = ((((SwitchNum + FringeNum) * MValue) / 100)) + MValue
GANum = (aSNum * GAb)
SNumber = CStr(GANum)
Rsr.CostRateTables("A").PayRates.Add iDate, SNumber,
SNumber, "0"

However, if there is already an entry on the same day as "iDate" the code
fails (last line of code).
Also, in the second line of the code I have "...Item("1")..." I know I am
not supposed to use a "1" there, but I am not sure how to call the first
entry of rsouce table A. How would I call the first entry?
 
R

Rod Gill

Hi,

Try:

Sub Test()
Dim CRT As CostRateTable
Dim D As Date
Dim PR As PayRate
Dim Res As Resource
D = CDate(InputBox("Enter Effective Date")) +
ActiveProject.DefaultStartTime
If D < CDate("1/1/2007") Then
MsgBox "Macro ended"
End
End If
For Each Res In ActiveProject.Resources
If Not Res Is Nothing Then
For Each CRT In Res.CostRateTables
For Each PR In CRT.PayRates
If PR.EffectiveDate = D Then
PR.Delete
End If
Next PR
Next CRT
End If
Next Res
End Sub



--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com




John4bank said:
In project I have developed a macro that burdens a base salary rate with
different overhead rates. My problem is that if someone had previously
entered a rate to take effect on the same day as the new burdens take
effect
I cannot overide the old rate.

Is there a way to cycle through all of the resources on the resource sheet
and delete all rate changes on ____ date or is there a way to simply
replace
them as the macro cycles through?

My current code looks like:
FringeNum = Val(SFringe)
CRate =
Rsr.CostRateTables("A").PayRates.Item("1").StandardRate
CRate = Replace(CRate, "$", "")
CRate = Replace(CRate, "/h", "")
MValue = Val(CRate)
aSNum = ((((SwitchNum + FringeNum) * MValue) / 100)) +
MValue
GANum = (aSNum * GAb)
SNumber = CStr(GANum)
Rsr.CostRateTables("A").PayRates.Add iDate, SNumber,
SNumber, "0"

However, if there is already an entry on the same day as "iDate" the code
fails (last line of code).
Also, in the second line of the code I have "...Item("1")..." I know I am
not supposed to use a "1" there, but I am not sure how to call the first
entry of rsouce table A. How would I call the first entry?

__________ Information from ESET Smart Security, version of virus
signature database 4787 (20100119) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4787 (20100119) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
J

John4bank

Thanks, I made some minor changes to customize it to my project and it works
perfectly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top