CODEWITHSUNDEEP
vbaexcel
Log On
Log On

Reputation: 97

Vlookup with a named range

I have a variable which takes the name of a workbook. I will be working through a list of these workbooks. For example,

dim unit_index as variant

unit_index = "unit_20444b_201608.xls"

I want to use a vlookup to return a value from the workbook assigned to unit_index. So when I enter the vlookup formula manually and record the macro I get the code below:

ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-1],unit_20444b_201608.xls!alpha_lookup,2,FALSE)"

I want the workbook name to change dynamically so I want the code to look something like this:

ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-1],**unit_index**!alpha_lookup,2,FALSE)"

alpha_lookup is the named range in unit_index.

So the workbook name would change as unit_index is updated. How can I make this work?

Upvotes: 1

Views: 381

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You need to try it like this...

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1]," & unit_index & "!alpha_lookup,2,FALSE)"

Upvotes: 2

Related Questions