CODEWITHSUNDEEP
excelvba
vipmaciej
vipmaciej

Reputation: 13

vba error when delete values in couple rows: Run-time error '13': Type mismatch

I have a problem with my VBA code. Generally, each line of code works as it should, but if I try to delete values (even empty cells) of at least two lines in the E column (select and delete), I get

Run-time error '13': Type mismatch

I read that it was because of not declaring a variable, but I don't know what is missing.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCellsKolumnE As Range

Set KeyCellsKolumnE = Range("E2:E100")

If Not Application.Intersect(KeyCellsKolumnE, Range(Target.Address)) _
    Is Nothing Then

    If Range(Target.Address).Value = "TEXT1" _
        Or Range(Target.Address).Value = "TEXT2" Then
            Range(Target.Address).Offset(, 3).Value = "TEXT3"

    ElseIf Range(Target.Address).Value = "TEXT4" _
        Or Range(Target.Address).Value = "TEXT5" _
        Or Range(Target.Address).Value = "TEXT6" Then
            Range(Target.Address).Offset(, 3).Value = "TEXT7"

    ElseIf Range(Target.Address).Value = "TEXT7" Then
            Range(Target.Address).Offset(, 3).Value = "TEXT7"
            Range(Target.Address).Offset(, 10).Value = "TEXT8"

    ElseIf Range(Target.Address).Value = "" Then
        Range(Target.Address).Offset(, 3).Value = ""

    Else
        Range(Target.Address).Offset(, 3).Value = ""
    End If

End If
End Sub

Upvotes: 0

Views: 563

Answers (1)

HTH
HTH

Reputation: 2031

As BigBen pointed out, the main issue should be the multicell Target, which calls for a loop

Also, you might want to ensure the multicell Target is compeletly inside column E

I also turned the If ElseIf EndIf syntax into a Select Case one

Finally, I throw in the good coding pratice to avoid multiple recursive callings in such an event handler 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCellsKolumnE As Range
    Set KeyCellsKolumnE = Range("E2:E100")

    If Not Application.Intersect(KeyCellsKolumnE, Target) Is Nothing And Target.Columns.CountLarge = 1 Then ' make sure Target is completely inside column E
        On Error GoTo SafeExit
        Application.EnableEvents = False ' disable events to prevent recursive calling

        Dim cel As Range
        For Each cel In Target ' loop through each Target cell
            With cel ' reference current cell
                Select Case .Value
                    Case "TEXT1", "TEXT2"
                        cel.Offset(, 3).Value = "TEXT3"

                    Case "TEXT4", "TEXT5", "TEXT6"
                        .Offset(, 3).Value = "TEXT7"

                    Case "TEXT7"
                        .Offset(, 3).Value = "TEXT7"
                        .Offset(, 10).Value = "TEXT8"

                    Case Else
                        .Offset(, 3).ClearContents

                End Select
            End With
        Next

    End If

SafeExit:
    Application.EnableEvents = True ' restore events back

End Sub

Upvotes: 3

Related Questions