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>

 

[LxQ]

maybe instead of .Value you can use .Text or .ToString because strClient should come up as a string, not a numerical value..

 

[docam84]

Thanks, but I am still getting an error. I think it's because I am deleting the row.

 

[RoryA]

Does Target consist of more than one cell?
Regards,
Rory

 

[docam84]

Initially, no, but I think that Excel is getting choked up when I delete the entire row, because it's triggering the worksheet change event.

 

[Norie]

Where/when do you get the error?

What are you actually doing with strClient?

 

[RoryA]

You might try disabling events before the deletion and then re-enabling them:

Application.EnableEvents = False
' do your thing
Application.EnableEvents = True

 

[docam84]

I get the error when the row is deleted. strClient is passed by reference to a procedure that determines which type of client it is.

 

[Norie]

And where do you get the error?

Because I don't see any code passing that variable to another routine.

By the way I think rorya could be on the right track.

 

[docam84]

Norie,

You were right about rorya's suggestion. It did work and I didn't get any errors although I was using the errorhandler to subdue the error, but it was annoying nonetheless.

Thanks!!