Insert row gives run-time error 13 message
 

Me again....

I'm using the below to insert a formula into column E if the corresponding
column D value is changed.... so far so good.

However, if the user inserts a line it gives a "Run-time error 13: type
mismatch" and highlights the "If Len(Target....." section of the macro...

Any ideas how to fix it?



Sub Worksheet_Change(ByVal Target As Range)

Dim r As Long

If Not Intersect(Range(Target.Address), Columns("D:D")) _
Is Nothing And Target.Row 5 Then

r = Target.Row

If Len(Target.Value) 0 Then

Cells(r, "E").FormulaR1C1 =
"=IF(IF(RC[-1]="""",RC[-2],RC[-1])=0,"""",IF(RC[-1]="""",RC[-2],RC[-1]))"

End If
End If

End Sub

Insert row gives run-time error 13 message
 

Hi,

This line at the start of the sub will cure the problem

If Target.Cells.Count 1 Then Exit Sub

Mike

"raphiel2063" wrote:

Me again....

I'm using the below to insert a formula into column E if the corresponding
column D value is changed.... so far so good.

However, if the user inserts a line it gives a "Run-time error 13: type
mismatch" and highlights the "If Len(Target....." section of the macro...

Any ideas how to fix it?



Sub Worksheet_Change(ByVal Target As Range)

Dim r As Long

If Not Intersect(Range(Target.Address), Columns("D:D")) _
Is Nothing And Target.Row 5 Then

r = Target.Row

If Len(Target.Value) 0 Then

Cells(r, "E").FormulaR1C1 =
"=IF(IF(RC[-1]="""",RC[-2],RC[-1])=0,"""",IF(RC[-1]="""",RC[-2],RC[-1]))"

End If
End If

End Sub