I like using the "Match" function since it can be set to find an exact match.
I believe the "Find" function requires the LookUp list ot be sorted ascending to
work right & may not guarantee an exact match.
Not sure if I follow your flow of events, but give this a try (I havent tested
it fully):

Sub testLookUpName2()
vGetTotalRowsInData = Range("A1").CurrentRegion.Rows.Count
For vRowNumber = 2 To vGetTotalRowsInData
vNameToLookUp = Sheets("Sheet1").Range("A" & vRowNumber).Value
If Not (IsEmpty(vNameToLookUp)) Then

If IsError(vRowMatch = WorksheetFunction.Match(vNameToLookUp,
Sheets("Sheet2").Range("D:D"), 0)) Then
'skip: not found
Else
vRowMatch = WorksheetFunction.Match(vNameToLookUp,
Sheets("Sheet2").Range("D:D"), 0)
vActualEmployeeName = Sheets("Sheet2").Range("E" & vRowMatch).Value
Sheets("Sheet1").Range("A" & vRowNumber).Offset(0, 26).Value = vNewEmployeeName
End If

End If
Next vRowNumber

End Sub