I am running a basic VLOOKUP to get an employees email address.
However, when the lookup value enters an employees name that doesn't exist in the array, it throws an error.
I added an "On Error Resume Next" but this just caused the remainder of the email addresses to be the last email address encountered, instead of continuing the search and using the current employee as the search variable.
What I want is when the search variable ProjectManName
doesn't exist in the array, to just skip over that line, and continue the search as normal. I will then go and fill in the empty email addresses manually.
Dim myLookupValue As Range
Dim strResult As String
Dim lngLastRow As Long
Dim lngLoop As Long
Dim ProjectManName As String
Set myLookupValue = Worksheets("Employees").Range("A1", Worksheets("Employees").Range("B1").End(xlDown))
With Worksheets("Project Summary")
lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For lngLoop = 2 To lngLastRow
On Error Resume Next
ProjectManName = .Cells(lngLoop, 1).Value
strResult = Application.WorksheetFunction.VLookup(ProjectManName, myLookupValue, 2, False)
Range("K" & lngLoop).Value = strResult
ActiveCell.Offset(1, 0).Select
Next
End With
Also, for the myLookupValue, do I need the second instance of Worksheets("Employees")? When I didn't do this it also gave an error because the second cell in the range looked at the current worksheet instead of the Employees worksheet.
Lastly, how can I replace "K" in Range("K" & lngLoop).Value
to be the first empty column?