Vba if iserror issue with vlookup

[Andy1993]

Hi,


I have a little problem here.


This vlookup code works fine.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const Material As String = "N2:N50"     
Const Part As String = "M2:M50"
Set M2 = Sheets("SEARCH").Range("M2")
Set Search = Sheets("PART LIST").Range("A:A")
Result = Application.VLookup(M2, Search, 1, False)
Find = Application.VLookup(Target.Value, Search, 1, False)
Find2 = Application.VLookup(Target.Offset(0, -1).Value, Search, 1, False)
    
    On Error GoTo ws_exit
    Application.EnableEvents = False


    If IsError(Result) Then
    Sheets("PICTURES").Range("A1").Value = "999-9999"
    End If
    
    If Not Intersect(Target, Me.Range(Material)) Is Nothing Then
        With Target
        If Target.Offset(0, -1).Value = Find2 Then
            Sheets("PICTURES").Range("A1").Value = Target.Offset(0, -1).Value
        End If
        End With
    End If
    
    
    
    If Not Intersect(Target, Me.Range(Part)) Is Nothing Then
        With Target
            If Target.Value = Find Then
            Sheets("PICTURES").Range("A1").Value = Target.Value
        End If
        End With
    End If




ws_exit:
    Application.EnableEvents = True
End Sub

But whenever match can't be found then this line calls

    If IsError(Result) Then
    Sheets("PICTURES").Range("A1").Value = "999-9999"
    End If

But the problem is that wherever i'm clicking not inside range N2:N50 or M2:M50 then this IsError function works also.


Is there a way to stop it doing IsError function if i click outside ranges?


Tried to use else option but it doesn't work.

[SamT]

 Set M2 = Sheets("SEARCH").Range("M2")
Set Search = Sheets("PART LIST").Range("A:A")

Result = Application.VLookup(M2, Search, 1, False)
    If IsError(Result) Then
       Sheets("PICTURES").Range("A1").Value = "999-9999"
       Exit Sub   '<----???
    End If

    On Error GoTo ws_exit
    Application.EnableEvents = False

   Find = Application.VLookup(Target.Value, Search, 1, False)
   Find2 = Application.VLookup(Target.Offset(0, -1).Value, Search, 1, False)
    
    If Not Intersect(Target, Me.Range(Material)) Is Nothing Then
        With Target
   '
   '

[Andy1993]

I sorted it out.
Added just extra check in IsError function if ranged area is selected.

If Not Intersect(Target, Me.Range(Material)) Is Nothing Then

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const Material As String = "N2:N50"
Const Part As String = "M2:M50"


Set M2 = Sheets("SEARCH").Range("M2")
Set Search = Sheets("PART LIST").Range("A:A")
    
    On Error GoTo ws_exit
    Application.EnableEvents = False
    
    Find = Application.VLookup(Target.Offset(0, -1).Value, Search, 1, False)
    
    If IsError(Find) Then
        If Not Intersect(Target, Me.Range(Material)) Is Nothing Then
            Sheets("PICTURES").Range("A1").Value = "999-9999"
        End If
    End If
    
    If Not Intersect(Target, Me.Range(Material)) Is Nothing Then
        With Target
        If Target.Offset(0, -1).Value = Find Then
            Sheets("PICTURES").Range("A1").Value = Target.Offset(0, -1).Value
        End If
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

[SamT]