Intersect Method encountering type mismatch #13-CodePudding

I am new to this forum and also not so familiar with the use of intersect method with worksheet change function. Here're my VBA codes for one particular worksheet I am working on:

Private Sub worksheet_change(ByVal Target As Range)

    If Not Intersect(Target, Range("B1:B10")) Is Nothing Then

        If Target = "XYZ pty ltd" or Target = "HXH Corporate" Then

            msgbox Target

        End If
    End If

End Sub

The codes work fine every time I copy and paste a new value in a cell within the target set. However, an error prompt (#13 type mismatch) starts appearing when I copy and paste a row (e.g. A1 through C1) of data over in the target sheet.

Is there a workaround to avoid this error?

Thanks

CodePudding user response：

a) Intersect is a function that gives all cells that are part of two ranges. It is often used to check if a specific cell is within a range (in your case B1:B10). The function returns Nothing if the ranges have no cell in common, and combined with the Not-operator, the If-statement will be true if they have cells in common.

b) Using Target as you do in If Target = "XYZ pty ltd" is a shortcut for If Target.Value = "XYZ pty ltd". That means VBA will read the content of the cell and compare it against a string. This can fail for 2 reasons, both giving the runtime error 13: (1) When the range contains more that one cell (because Target.Value is now an array of values and in VBA, you can't compare an array with a single value in one go), and (2) if VBA cannot convert the content of a cell into a string - that's the case if a cell contains an Error (#NA, #NAME?...)

Your event routine gets Target as parameter, that is a Range containing all cells that where modified. You will need to loop over all cells individually:

Option Explicit

Private Sub worksheet_change(ByVal Target As Range)

    If Intersect(Target, Range("B1:B10")) Is Nothing Then Exit Sub
    
    Dim cell As Range
    ' Check only cells that are relevant
    For Each cell In Intersect(Target, Range("B1:B10"))
        If Not IsError(cell.Value) Then
            If Target = "XYZ pty ltd" Or Target = "HXH Corporate" Then
                ' ... (do your magic here)
            End If
        End If
    End If
End Sub