CODEWITHSUNDEEP
excelvbaerror-handling
Andrei Ion
Andrei Ion

Reputation: 1827

Invalid procedure call or argument error in VBA

I get this error and I really don't know why.

I have the following piece of code:

Rand = 2
LRand = ws.Cells(Rows.Count, 6).End(xlUp).Row
Do While Rand <> LRand + 1
    If ws.Cells(Rand, 4).Value = "" Then
        desch = InStr(ws.Cells(Rand, 5).Value, "(")
        inch = InStr(ws.Cells(Rand, 5).Value, ")")
        ws.Cells(Rand, 4).Value = Mid(ws.Cells(Rand, 5).Value, desch + 1, inch - desch - 1) 'here is the error
    End If
Rand = Rand + 1
Loop

Do you guys have any idea why I get the invalid procedure call or argument? Thanks a lot!

Upvotes: 0

Views: 11015

Answers (2)

niko
niko

Reputation: 9393

it is possible 

 if desch = 0
      or 
 if inch = 0

 desch = instr('this may return zero')
 inch  = instr('when it doesnt find the substring')

 so putting them in mid functions results like these

 mid(string,0,0) results error  
 mid(string(0,2) results error
 mid(string(2,0) results error

So check those values at first

Upvotes: 3

GTG
GTG

Reputation: 4954

This code assumes that the contents of the cell being processed, ws.Cells(Rand, 5).Value, are on the form "(some text here)". If that assumption is wrong, the above error will occur. This will happen if the cell is empty, either ( or ) is missing or if ) comes before (.

Upvotes: 1

Related Questions