Results 1 to 11 of 11

Thread: Getting Run Time Error 5 on String?

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Jan 2018
    Location
    Nova Scotia
    Posts
    83
    Location

    Getting Run Time Error 5 on String?

    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
    Last edited by SamT; 01-31-2018 at 03:16 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •