[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Apply Excel VLOOKUP in VBA

Posted on 2012-09-13
Medium Priority
Last Modified: 2016-10-21
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.


Any ideas?
Question by:Blowfelt82
LVL 50
ID: 38394212

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
LVL 35

Accepted Solution

Norie earned 2000 total points
ID: 38394843
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.

Author Comment

ID: 38394884
The second option looks good to me! But I am not sure how to configure this to do the lookup in the other sheet?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 35

Expert Comment

ID: 38394901
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.

Author Closing Comment

ID: 38394927
Sorry didnt refresh my screen didnt see your previous post. The points are yours thanks for your help.
LVL 35

Expert Comment

ID: 38394947
Oops, I thought by 2nd option you meant me.:)

Expert Comment

by:nidhin johny
ID: 41853506
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 .

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question