In VBA Excel 2007, is there a substitution for VLookup?

[RidgeView]

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....

 

[Kevin B]

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])

 

[RidgeView]

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,
"O521", 2)
MsgBox Range("A1").Formula

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

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]

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("O521")
end with

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

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

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,
"O521", 2)
MsgBox Range("A1").Formula

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

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....

 

[RidgeView]

Works Perfect !!!! Thanks alot...

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("O521")
end with

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

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

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,
"O521", 2)
MsgBox Range("A1").Formula

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


:

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....