How to use target.row in vba excel with vlookup formulas

[Leorand]

Hello Guys, I got some question about the tittle above.
I want to make VBA program to match data using IFERROR(VLOOKUP) formulas, and the code like this :

Set TableRange = Range("D4:D10000")
result = Application.IfError(Application.VLookup(TableRange, Sheet5.Range("A2:C170"), 3, False), "")

If Range("E" & Target.Row).Value = "" Then
    Range("E" & Target.Row).Value = result
End If

In this code I use Target.Row function to make whenever the D row has data, E row will show the match data using Vlookup formulas.
For the first Input in row D4, row E4 showing the match data, but when it goes to row D5 with different data, row E5 still showing the same data as row E4.
Like this :

So how do I solve this case? Is there are another method?
I need your help guys.
It will help me a lot.

Best Regards

 

[Legacy 143009]

I don't think you can use a range as lookup value. One good practice could be:

Set TableRange = Range("D4:D10000")
If Not Intersect(Target, TableRange) Is Nothing Then
  result = Application.IfError(Application.VLookup(Target.Value, Sheet5.Range("A2:C170"), 3, False), "")
End If
If Range("E" & Target.Row).Value = "" Then
    Range("E" & Target.Row).Value = result
End If

 

[Leorand]

I don't think you can use a range as lookup value. One good practice could be:

Set TableRange = Range("D4:D10000")
If Not Intersect(Target, TableRange) Is Nothing Then
  result = Application.IfError(Application.VLookup(Target.Value, Sheet5.Range("A2:C170"), 3, False), "")
End If
If Range("E" & Target.Row).Value = "" Then
    Range("E" & Target.Row).Value = result
End If

Thank you for your help Flashbond.
The code work perfectly!!

 

[Legacy 143009]

Glad it did help! Thanks for the feedback

EDIT: It would be wise to move If block inside the Intersect.

Set TableRange = Range("D4:D10000")
If Not Intersect(Target, TableRange) Is Nothing Then
  result = Application.IfError(Application.VLookup(Target.Value, Sheet5.Range("A2:C170"), 3, False), "")
  If Range("E" & Target.Row).Value = "" Then
      Range("E" & Target.Row).Value = result
  End If
End If

 

[Leorand]

Glad it did help! Thanks for the feedback

EDIT: It would be wise to move If block inside the Intersect.

Set TableRange = Range("D4:D10000")
If Not Intersect(Target, TableRange) Is Nothing Then
  result = Application.IfError(Application.VLookup(Target.Value, Sheet5.Range("A2:C170"), 3, False), "")
  If Range("E" & Target.Row).Value = "" Then
      Range("E" & Target.Row).Value = result
  End If
End If

Yeah, I just change the

TableRange

into

Target.Value

, for the rest code is work's well.