Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA Excel 2007, is there a substitution for VLookup?
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.... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA Excel 2007, is there a substitution for VLookup?
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.... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA Excel 2007, is there a substitution for VLookup?
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.... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA Excel 2007, is there a substitution for VLookup?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
In VBA Excel 2007, is there a substitution for VLookup?
Works Perfect !!!! Thanks alot...
"Dave Peterson" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup in excel 2007 | Excel Discussion (Misc queries) | |||
More than i substitution | Excel Discussion (Misc queries) | |||
Substitution | Excel Discussion (Misc queries) | |||
substitution | Excel Discussion (Misc queries) | |||
Substitution | Excel Discussion (Misc queries) |