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]

:thumb