VB Excel VLOOKUP

Private Sub Worksheet_Change(ByVal Target As range)

Set WB = ThisWorkbook

Set WT = WB.Worksheets("TEAM")

Set WC = WB.Worksheets("COACH")

lrow = range("E" & Rows.Count).End(xlUp).Row

nrow = range("F" & Rows.Count).End(xlUp).Row

On Error GoTo MyErrorHandler:

If Not Intersect(Target, WT.range("E:E")) Is Nothing Then

'MsgBox "Changed my range!"

d = 2

For i = 2 To lrow

If range("E" & i) <> "" Then

WT.range("F" & i).Value = WorksheetFunction.

IfError((WorksheetFunction.

VLookup(WT.range("E" & i),

WC.range("A2:D50"), 3, 0)), "")

d = d + 1

Else

WT.range("F" & i).Value = ""

d = d + 1

End If

Next i

If WT.range("F" & i) <> "" And WT.range("E" & i) = "" Then

WT.range("F" & i).ClearContents

d = d + 1

End If

End If

For n = 2 To nrow

If WT.range("F" & nrow) <> "" And WT.range("E" & nrow) = "" Then

WT.range("F" & nrow).ClearContents

End If

Next n

MyErrorHandler:

If Err.Number = 1004 Then

range("F" & i) = "Not available"

End If

End Sub