Apply Excel VLOOKUP in VBA

Posted on 2012-09-13
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

    Expert Comment


    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 33

    Accepted Solution

    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

    The second option looks good to me! But I am not sure how to configure this to do the lookup in the other sheet?
    LVL 33

    Expert Comment

    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

    Sorry didnt refresh my screen didnt see your previous post. The points are yours thanks for your help.
    LVL 33

    Expert Comment

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

    Expert Comment

    by:nidhin johny
    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 how can i implement this .

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now