Error in Evaluate when selecting more than one cell: Run-time error '13': Type Mismatch

Question

I evaluate for valid emails in Column M and the code works...

Except when I do anything anywhere in the sheet, Column M or elsewhere, that involves more than one cell - such as copying or pasting multiple cells, selecting and deleting multiple rows, etc., I get the error message

"Run-time error '13': Type Mismatch"

Debugging points to this code.

If Target.Column = 13 And Target.Value <> "" And _
  Evaluate("COUNTIF(" & Target.Address & ",""*@*.*"")") <> 1 Then
    Target.ClearContents
    Target.Activate
    MsgBox "Please enter a valid email address."
End If

I'd like to keep the code but prevent the errors when I am only deleting or pasting multiple cells.

Storax Storax · Accepted Answer · 2020-01-26T08:49:33

You can check with target.Cells.CountLarge if more than one cell is selected or to add it to your code it could look like that

If Target.Cells.CountLarge = 1 Then
    ' Your code goes here
End If

Update: Your code should look like that

If Target.Cells.CountLarge = 1 Then

    ' This is your code, right?
    If Target.Column = 13 And Target.Value <> "" And Evaluate("COUNTIF(" & Target.Address & ",""*@*.*"")") <> 1 Then
        Target.ClearContents
        Target.Activate
        MsgBox "Please enter a valid email address."
    End If

End If

Remark: As the OP did not post his complete code I assume this post was taken into consideration

Error in Evaluate when selecting more than one cell: Run-time error '13': Type Mismatch

1 Answers