Getting Run Time Error 5 on String?

[Sully1440]

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

[Dave]

U can trial placing this line of code before the error line...

Msgbox ArgString

Maybe there is no value for ArgString as U set it to vbnullstring and then maybe the instr argument didn't create a new value? HTH. Dave
ps. Please use code tags

[Sully1440]

Hi Dave,
Thanks for getting back to me. I'm really stuck on this.
You're right... the ArgString has no value which is causing the error (I used your trick to place MsgBox Argstring before the error). I wrote an If Statement to bypass the error, but the problem is that I need the value to work and I'm not sure why it's coming back as nothing. The code brings back address values for every other one but this cell and I have no idea why....I'm racking my brain on this one.

Can you help?
Jim

[Dave]

Maybe...

If InStr(1, PredecessorList.Offset(i, 0), activitylist.Offset(i, 0)) > 0 Then

HTH. Dave

[Sully1440]

Hi Dave,
No it didn't work. It's stops on other cells now when I try that.

[Dave]

For k = 1 To NumRows 
For i = 1 To k - 1

You've got quite a bit going on there. Maybe trial this to see if i or k value is 0...

Msgbox ArgString & " I " & I &   " K " & k
If InStr(1, PredecessorList.Offset(i, 0), activitylist.Offset(k, 0)) > 0 Then

HTH. Dave

[Paul_Hossler]

"If it's not the program, it's the Data"


1. Suggest breaking the colon-connected statements into separate lines so you can use debugging easier


2. Use the debugging to step through the code and/or check variables and logic

http://www.cpearson.com/excel/DebuggingVBA.aspx


http://www.dummies.com/programming/v...shortcut-keys/

3. Create a workbook with just enough data and the macros to demonstrate the issue, and post it here so people can experiment; strip/change any sensitive data


4. This doesn't do what you think it does: I, k, n as Typed as Variant, NumRows is an Integer

Each variable needs to be explicit, and Integer should almost always be replaced by Long

 Dim i, k, n, NumRows As Integer: Dim ArgString As String: Dim SucString As String

Should be something like this

Dim I As Long, k as Long, n As Long, NumRows As Long
Dim ArgString As String, SucString As String

5. I used Option Explicit at the tope of each and every module to help me avoid typos and other silly mistakes; some people don't but I like to

[Sully1440]

Thanks Paul & Dave. I've been trying what you suggested but I'm failing to get this to work.

I uploaded the file as you suggested and stripped out any sensitive company stuff. I think that the ArgString is empty and it doesn't know what to do.....(and neither do I).

Can you have a look at my attachment?
Jim

[Paul_Hossler]

This might be a band-aide to avoid the error, but the result looks OK

I did change / reformat the macros a little to make it easier for me to follow

Option Explicit

Dim ActivityList As Range, slackList As Range, CriticalList As Range, DurationList As Range
Dim PredecessorList As Range, EarlyStartList As Range, SuccessorList As Range

Sub RunCPM()
    Dim NumRows As Long, k As Long
    Call ForwardPass
    Call BackwardPass
    
    With ActiveSheet
        Set ActivityList = Range("A7")
        Set slackList = Range("I7")
        Set CriticalList = Range("J7")
        Set DurationList = Range("D7")
            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 As Long, k As Long, NumRows As Long
    Dim ArgString As String
    
    With ActiveSheet
        Set ActivityList = Range("A7"): Set PredecessorList = Range("C7")
        Set EarlyStartList = Range("E7"): Set SuccessorList = Range("K7")
        
        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 As Long, k As Long, n As Long, NumRows As Long
    Dim ArgString As String, SucString As String
    
    Set ActivityList = Range("A7")
    Set PredecessorList = Range("C7")
    Set EarlyStartList = Range("E7")
    Set SuccessorList = Range("K7")
    
    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
                    Debug.Print n & " -- " & k & " -- " & ArgString
                    ArgString = ArgString & EarlyStartList.Offset(i, 2).Address & ","
                    SucString = SucString & ActivityList.Offset(i, 0).Address & ","","","
                End If
            Next I
        
            If Len(ArgString) > 0 Then
                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 & ")"
            End If
        Next n
    End With
End Sub

[Sully1440]

Yes, it works to avoid the error...thank you
I don't have to worry about it stopping for new projects.
The only problem is that there should be values for LS and LF.

[Sully1440]

Tried this a few more times with other projects. Quite happy it works well.

Paul, Dave,
Appreciate the help. Thank you again.
Jim