Andy1993
06-27-2021, 11:19 AM
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.
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.