I am trying to do a Vlookup. My lookup value is on the MainData sheet and my
lookup range is on the DeptLookup sheet. If there is a match, the code works
fine. However, if there is no match, I get a Runtime error '1004' -
Application defined or object defined error on the line

y = WorksheetFunction.VLookup(ActiveCell.Offset(0, -31).Value, rng, 1, False

My code is:
Set wb = Workbooks("TestWorkbook.xls")
Set ws = wb.Sheets("DeptLookup")
Set WS2 = wb.Sheets("MainData")
Set rng = ws.Range("$A$3:$C$59")
Dim y As Variant

y = WorksheetFunction.VLookup(ActiveCell.Offset(0, -31).Value, rng, 1,
False)
If Not IsError(y) Then
ActiveCell.Offset(0, -34).Value =
Application.WorksheetFunction.VLookup_(ActiveCell. Offset(0, -31).Value, rng,
1, False)

Would anyone be able to tell me what I am doing wrong?

Thanks in advance!