Run-time error 13 when deleting/inserting multiple row

[alno00]

Hello everybody!

New forum user here who is also very new when it comes to coding in Excel. I have encountered an issue where I get Run-time error 13 when deleting or inserting multiple rows.

Basically, I want my code to add "-" in a few columns when "TF" is entered in column A. If "TF" is removed, the dashes should also disappear.

I tried using the code from this post with no luck: https://www.mrexcel.com/forum/excel...ror-13-type-mismatch-when-deleting-cells.html

My code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Column = 1 And Target.Column Mod 2 >= 1 And Target.Row >= 3 Then
    For Each c In Target
        If c.Value = "TF" Then
            c.Offset(0, 11).Value = "-"
            c.Offset(0, 12).Value = "-"
            c.Offset(0, 13).Value = "-"
            c.Offset(0, 14).Value = "-"
        Else
            c.Offset(0, 11).Value = ""
            c.Offset(0, 12).Value = ""
            c.Offset(0, 13).Value = ""
            c.Offset(0, 14).Value = ""
        
        End If
        
    Next c
End If
End Sub

Does anybody know what I'm doing wrong?

 

[Dryver14]

Why are the extra parameters there after if target.column = 1

 

[wideboydixon]

If you're changing the values in Worksheet_Change() event, it's going to try and call Worksheet_Change() again in the middle of your code. You'll need to turn off application events I think:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim columnA As Range
Dim changedCell As Range
Dim thisCol As Long

' Find the intersection of column A and the changed range
Set columnA = Application.Intersect(Range("A:A"), Target)

' If nothing falls in this range then exit
If columnA Is Nothing Then Exit Sub

' Disable application events because we're going to change some values
Application.EnableEvents = False

' Process all changed cells in column A
For Each changedCell In columnA
    ' Must be on row 3 or below
    If changedCell.Row >= 3 Then
        ' Change columns L to O
        For thisCol = 11 To 14
            ' New value depends on whether the changed cell contains "TF" or not
            changedCell.Offset(0, thisCol).Value = IIf(changedCell.Value = "TF", "-", "")
        Next thisCol
    End If
Next changedCell

' Turn on events again
Application.EnableEvents = True

End Sub

WBD

 

[steve the fish]

You could try this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim c As Range, rng As Range

Set rng = Intersect(Target, Range(Cells(3, 1), Cells(Rows.Count, 1)))
If Not rng Is Nothing Then
    For Each c In rng
        If LCase(c.Value) = "tf" Then
            Range(c.Offset(0, 11), c.Offset(0, 14)) = "-"
        Else
            Range(c.Offset(0, 11), c.Offset(0, 14)) = ""
        End If
    Next c
End If

End Sub

 

[alno00]

Thank you very much Steve (and the rest of you)!
Seems to work just fine