Solved: Run-Time Error 5 Invalid proc. call or argument

[YellowLabPro]

This is a solution derived from one of our gurus here. I dont fully understand how it works, so I cannot troubleshoot this. It was working fine before, but I added some code, and this may be causing the error.

The variable i, is showing zero (0) value in the Locals window.
The line in red is the line breaking.

The jpg attached shows the function, and the locals window w/ pertinent info.

Function DelChar(Cel As Range, ParamArray CharPos())
Dim Tmp As String, i As Long

Tmp = Cel
Debug.Print Cel

For i = LBound(CharPos) To UBound(CharPos)

Tmp = Left(Tmp, CharPos(i) - 1) & Right(Tmp, Len(Tmp) - CharPos(i))
Debug.Print Tmp
Next

Cel.Value = Tmp

End Function

[rory]

This bit:
Right(Tmp, Len(Tmp) - CharPos(i))

appears to evaluate to:
Right(tmp, -1)

which won't work!

[YellowLabPro]

Rory,
I believe you are correct. If I understand your answer.

The problem lies in here - The case Select is evaluating which is the longest string in a range, this particular string is 14 characters long,
DC108SUPASBWTSbut since the Case Select is for Case 17, the longest value, I had a value to remove the 15th character, which is not possible, -1 value. Hence, Rory, your summation of the problem is correct. But I did not understand the code until I stepped through it several times and substituted in what I knew was working.


Case 13: Call DelChar(.Cells(i, "W"), 9)

Case 14: Call DelChar(.Cells(i, "W"), 10, 8)

Case 15: Call DelChar(.Cells(i, "W"), 12, 9, 7)

Case 16: Call DelChar(.Cells(i, "W"), 13, 9, 8, 4)

Case 17: Call DelChar(.Cells(i, "W"), 15, 13, 9, 8, 4)



Thanks for helping me solve the problem.

[rory]

Glad to help. You might want to add a check to use:


Right(Tmp, Application.Max(0, Len(Tmp) - CharPos(i)))