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/visual-basic/vba-debugging-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