Apply Excel VLOOKUP in VBA

I have the following formula I use in Excel, what I am looking for is a way I can programatically apply this to a cell using VBA. Identities is the name of a sheet I am referencing.

=VLOOKUP(B2,Identities!A:C,3,FALSE)

Any ideas?
Blowfelt82Asked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
This will put the formula in the active cell.
ActiveCell.Formula = "=VLOOKUP(B2,Identities!A:C,3,FALSE)"

Open in new window

If that's not quite what you want post back.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

first, using Vlookup with whole column references is not a good idea, since it will slow down your workbook considerably. Ringfence the lookup range to the used cells only. Have a look at dynamic range names.

How do you want to "apply" the vlookup? Do you want the VBA to write the formula into the cell? If so, you can use code along the lines of

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[3],RC[6]:R[2]C[7],2,FALSE)"

Or do you want to write the result of the Vlookup into a variable in VBA? In that case, use something along the lines of

MyVariable = Application.WorksheetFunction.VLookup(Range("D1"), Range("G1:H3"), 2, False)

All of this is highly configurable when it comes to ranges and how to address them, of course.

cheers, teylyn
0
 
Blowfelt82Author Commented:
The second option looks good to me! But I am not sure how to configure this to do the lookup in the other sheet?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
NorieVBA ExpertCommented:
I don't quite follow.

The other sheet Identies is referenced in the formula so the range A:C on that sheet will be used as the lookup range.

Nothing needs to be 'configured' for that.
0
 
Blowfelt82Author Commented:
Sorry didnt refresh my screen didnt see your previous post. The points are yours thanks for your help.
0
 
NorieVBA ExpertCommented:
Oops, I thought by 2nd option you meant me.:)
0
 
nidhin johnyCommented:
i have 2 columns named  id and name in sheet1 without data and i have column with id and name in sheet2 with data .using vlookup how can i programatically  fill sheet1 id when i type name in adjacent cell .i searched lot but i cant find. i am using open xml and c# for reading excel.so how can i implement this .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.