Dim EmpName as String
dim LookUpRng as range
dim res as variant

empname = worksheets("Employee Periods 2-13").Cells(14, 1).value

with worksheets("drop-down lists")
set lookuprng = .range("O5:p21")
end with

res = application.vlookup(empname, lookuprng,2, false)

if iserror(res) then
msgbox "Not found"
else
msgbox res
end if



RidgeView wrote:

I am getting a null result...What am I doing wrong? I verified through
debugger that I am pulling a good name which is in the "O" column. The
information after "Vlookup" is all one line.

ActiveWorkbook.Sheets("Employee Periods 2-13").Select
EmpName = Cells(14, 1) ' This does grab the correct name

ActiveWorkbook.Sheets("Drop-Down Lists").Select
Range("A1").Formula = Application.WorksheetFunction.VLookup(EmpName,
"O5:P21", 2)
MsgBox Range("A1").Formula

"Kevin B" wrote:

Use the following, substituting the range the formula is assigned to and the
arguments with the cell references you're using:

Range("A1").Formula = Application.WorksheetFunction.VLookup(Arg1, Arg2,
Arg3, [Arg4])

--
Kevin Backmann


"RidgeView" wrote:

I did not realize that VLookup is not a viable function in Excel 2007 VBA.
Is there a substitute instruction? Currently I am doing a FOR/NEXT routine.
Thanks in Advance....

--

Dave Peterson