Vlookup in vba using dynamic ranges

    [xpost][/xpost]


    Hello I have two files: an input file and output file. I want to apply vlookups in 16 columns from the input file into the output file. I have written a code in which i am first copying the input data into the output file and then applying the vlookups. I am facing an issue in making the vlookups dynamic. For example, I want to change the lookup value (rows) and the columns in which the formula is being applied. I tried to make it dynamic using a loop and incrementing the values, but did not have much luck. Any help would be appreciated. Files attached for reference. Input file name: Input File Output file name: FY20Q4_Spend_Sponsorships Sheet in which lookups to be executed: Dynamics Tracker Input sheet where data is being pasted: Data sheet Column references are updated in the first row of the Dynamics Tracker sheet for reference.

    Quote from sahilkh18

    For example, I want to change the lookup value (rows) and the columns in which the formula is being applied. I tried to make it dynamic

    1) what is the rule behind the "dynamic" way you want to achieve

    2) do you already want to have the result or do you want to apply the formula into the cell

    3) I assume you did not test the code because it is failing at "Workbooks("Input File").Close"

    4) what kind of error do you expect inside the for-loop (you used "on error resume next")

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login