Sully1440
01-31-2018, 01:18 PM
Hi All,
I'm trying to run a code to calculate Critical Path (that I found online). I can run the code for some projects, but one project results in a Run Time Error 5: Invalid Procedure Call or Argument.
I can't figure out what to change to make it work... It hangs up on a line in the Sub BackwardsPass() and the line is: ArgString = Left(ArgString, Len(ArgString) - 1)
Here is the code:
Sub RunCPM()
Call ForwardPass
Call BackwardPass
With ActiveSheet
Set activitylist = Range("A6"): Set slacklist = Range("I6"): Set CriticalList = Range("J6")
Set durationlist = Range("D6")
With activitylist
NumRows = Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count
End With
For k = 1 To NumRows
slacklist.Offset(k, 0).Formula = "=RC[-1]-RC[-3]"
CriticalList.Offset(k, 0).Formula = "=IF(RC[-1]=0,""YES"",)"
Next
End With
End Sub
Sub ForwardPass()
Dim i, k, NumRows As Integer: Dim ArgString As String
With ActiveSheet
Set activitylist = Range("A6"): Set PredecessorList = Range("C6")
Set EarlyStartList = Range("E6"): Set SuccessorList = Range("K6")
With activitylist
NumRows = Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count
End With
For k = 1 To NumRows
ArgString = vbNullString
If Len(PredecessorList.Offset(k, 0)) > 0 Then
For i = 1 To k - 1
If InStr(1, PredecessorList.Offset(k, 0), activitylist.Offset(i, 0)) > 0 Then
ArgString = ArgString & EarlyStartList.Offset(i, 1).Address & ","
End If
Next
End If
If Len(ArgString) = 0 Then
ArgString = 0
End If
EarlyStartList.Offset(k, 0).Formula = "=MAX(" & ArgString & ")"
EarlyStartList.Offset(k, 1).Formula = "=RC[-2]+RC[-1]"
Next k
End With
End Sub
Sub BackwardPass()
Dim i, k, n, NumRows As Integer: Dim ArgString As String: Dim SucString As String
Set activitylist = Range("A6"): Set PredecessorList = Range("C6")
Set EarlyStartList = Range("E6"): Set SuccessorList = Range("K6")
With ActiveSheet
With activitylist
NumRows = Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count
End With
EarlyStartList.Offset(NumRows, 3).Formula = "=" & EarlyStartList.Offset(NumRows, 1).Address
EarlyStartList.Offset(NumRows, 2).Formula = "=RC[+1]-RC[-3]"
For n = 1 To NumRows - 1
ArgString = vbNullString
SucString = vbNullString
k = NumRows - n
For i = k + 1 To NumRows
If InStr(1, PredecessorList.Offset(i, 0), activitylist.Offset(k, 0)) > 0 Then
ArgString = ArgString & EarlyStartList.Offset(i, 2).Address & ","
SucString = SucString & activitylist.Offset(i, 0).Address & ","","","
End If
Next i
'On Error Resume Next
ArgString = Left(ArgString, Len(ArgString) - 1)
EarlyStartList.Offset(k, 3).Formula = "=MIN(" & ArgString & ")"
EarlyStartList.Offset(k, 2).Formula = "=RC[+1]-RC[-3]"
SuccessorList.Offset(k, 0).Value = "=concatenate(" & SucString & ")"
Next n
End With
End Sub
I'm trying to run a code to calculate Critical Path (that I found online). I can run the code for some projects, but one project results in a Run Time Error 5: Invalid Procedure Call or Argument.
I can't figure out what to change to make it work... It hangs up on a line in the Sub BackwardsPass() and the line is: ArgString = Left(ArgString, Len(ArgString) - 1)
Here is the code:
Sub RunCPM()
Call ForwardPass
Call BackwardPass
With ActiveSheet
Set activitylist = Range("A6"): Set slacklist = Range("I6"): Set CriticalList = Range("J6")
Set durationlist = Range("D6")
With activitylist
NumRows = Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count
End With
For k = 1 To NumRows
slacklist.Offset(k, 0).Formula = "=RC[-1]-RC[-3]"
CriticalList.Offset(k, 0).Formula = "=IF(RC[-1]=0,""YES"",)"
Next
End With
End Sub
Sub ForwardPass()
Dim i, k, NumRows As Integer: Dim ArgString As String
With ActiveSheet
Set activitylist = Range("A6"): Set PredecessorList = Range("C6")
Set EarlyStartList = Range("E6"): Set SuccessorList = Range("K6")
With activitylist
NumRows = Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count
End With
For k = 1 To NumRows
ArgString = vbNullString
If Len(PredecessorList.Offset(k, 0)) > 0 Then
For i = 1 To k - 1
If InStr(1, PredecessorList.Offset(k, 0), activitylist.Offset(i, 0)) > 0 Then
ArgString = ArgString & EarlyStartList.Offset(i, 1).Address & ","
End If
Next
End If
If Len(ArgString) = 0 Then
ArgString = 0
End If
EarlyStartList.Offset(k, 0).Formula = "=MAX(" & ArgString & ")"
EarlyStartList.Offset(k, 1).Formula = "=RC[-2]+RC[-1]"
Next k
End With
End Sub
Sub BackwardPass()
Dim i, k, n, NumRows As Integer: Dim ArgString As String: Dim SucString As String
Set activitylist = Range("A6"): Set PredecessorList = Range("C6")
Set EarlyStartList = Range("E6"): Set SuccessorList = Range("K6")
With ActiveSheet
With activitylist
NumRows = Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count
End With
EarlyStartList.Offset(NumRows, 3).Formula = "=" & EarlyStartList.Offset(NumRows, 1).Address
EarlyStartList.Offset(NumRows, 2).Formula = "=RC[+1]-RC[-3]"
For n = 1 To NumRows - 1
ArgString = vbNullString
SucString = vbNullString
k = NumRows - n
For i = k + 1 To NumRows
If InStr(1, PredecessorList.Offset(i, 0), activitylist.Offset(k, 0)) > 0 Then
ArgString = ArgString & EarlyStartList.Offset(i, 2).Address & ","
SucString = SucString & activitylist.Offset(i, 0).Address & ","","","
End If
Next i
'On Error Resume Next
ArgString = Left(ArgString, Len(ArgString) - 1)
EarlyStartList.Offset(k, 3).Formula = "=MIN(" & ArgString & ")"
EarlyStartList.Offset(k, 2).Formula = "=RC[+1]-RC[-3]"
SuccessorList.Offset(k, 0).Value = "=concatenate(" & SucString & ")"
Next n
End With
End Sub