1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
| Function CalcURate(Week As Range, Resource As Range)
'calculate the utilization rate
Dim DayOne As String
Dim DayLoad As Integer
Dim WeekLoad As Integer
Dim Rate As Variant
Dim ColStart As String
Dim RowStart As String
ColStart = Columnletter(Week)
RowStart = Resource.Row
DayOne = ColStart & RowStart
For i = 0 To 4
Range(DayOne).Select
'move to the right
ActiveCell.Offset(0, i).Select
If Range(GetCellName(Columnletter(ActiveCell), ActiveCell.Row)).Value = "A" Then
DayLoad = 0
TestEnd = "OK"
While TestEnd <> "x"
'move to the down
ActiveCell.Offset(1, 0).Select
If Range(GetCellName(Columnletter(ActiveCell), ActiveCell.Row)).Value <> "x" Then
DayLoad = DayLoad + Range(GetCellName(Columnletter(ActiveCell), ActiveCell.Row)).Value
End If
TestEnd = Range(GetCellName(Columnletter(ActiveCell), ActiveCell.Row)).Value
Wend
End If
WeekLoad = WeekLoad + DayLoad
Next
'perform the rate calculation
MaxWeek = 40
Rate = WeekLoad * 100 / MaxWeek
CalcURate = CStr(Rate) & "%"
End Function
Function Columnletter(Optional rng As Range) As String
'Returns the Column Letter of the top left cell in rng.
If rng Is Nothing Then Set rng = Application.Caller
Columnletter = Left(rng.Address(0, 0), IIf(rng.Column > 26, IIf(rng.Column > 702, 3, 2), 1))
End Function
Function GetCellName(strCellLettter, intCellNumber)
GetCellName = strCellLettter + VBA.Right(VBA.Str(intCellNumber), VBA.Len(VBA.Str(intCellNumber)) - 1)
End Function |
Partager