Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 21
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,316
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 21
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 21
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup in excel 2007 Old Keith Excel Discussion (Misc queries) 2 November 27th 08 05:57 AM
More than i substitution Otto Moehrbach Excel Discussion (Misc queries) 10 November 20th 07 12:50 AM
Substitution Mitchell Excel Discussion (Misc queries) 4 December 19th 06 08:22 PM
substitution Cossloffe Excel Discussion (Misc queries) 1 June 4th 06 07:10 AM
Substitution Boenerge Excel Discussion (Misc queries) 2 May 23rd 05 12:14 PM


All times are GMT +1. The time now is 09:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"