Type Mismatch error

[docam84]

Hello,

I am having trouble trying to prevent this error from popping up and I was wondering if anyone could give me any clue as to why this is occurring.

My code checks to see if a cell contains a certain status, such as Approved or Rejected. If the status is Approved, then the procedure copies the row and pastes it onto a different worksheet within the same workbook. The row is then deleted (in the ChangeStatus procedure).

Here is my code:

<code>
Dim strClient As String

<font color = "blue">On Error GoTo ErrHandler</font>

<font color = "blue">If Not Intersect</font>(Target, Range("L2:L500")) Is Nothing Then

     strClient = Target.Offset(0, -10).Value

     Call ChangeStatus(Target.Row, Target.Value)

End If

Exit Sub

ErrHandler:

' // statements for error handler

Exit Sub
</code>

 

[Norie]

If you mean rorya's suggestion regarding turning off events then that's not quite right.

That wouldn't prevent the error occurring when you delete a row.

The problem is that if you are deleting a row then the Target is the whole row and that can't be put in a string.

A string can only hold a single value.

 

[docam84]

Hmm, I see. So, how would I circumvent this issue? Should I set <code> Target = Nothing </code> if it selects the entire row?

 

[Norie]

No.

You need to check the no of cells in Target.

If Target.Count > 1 Then Exit Sub

 

[docam84]

Norie,

Thanks so much, this seems to work really well.