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

Question

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

But the real problem is that this will fail if Target is a multi-cell range, because then .Value is a 2D array. You need to loop through each cell. — BigBen
– BigBen, Commented Apr 2, 2020 at 12:17

HTH · Accepted Answer · 2020-04-02 13:19:53Z

3

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

edited Apr 2, 2020 at 13:19

answered Apr 2, 2020 at 12:20

HTH

2,0311 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HTH Over a year ago

@BigBen, actually you should be right: OP's question tells about some multi cells attempt. That brings also the need to check the target is completely inside column E, or the Offsets would scatter values all over the sheet. I'll edit the answer. Thanks

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related