Excel range of cells: setting the range as a variable

[Zukkster]

I want to create a vlookup calculation in a cell using code. The problem is that the range used in the vlookup calculation can change. Therefore I need to select a range of cells and then use this range in a variable to be inserted into the line of code that contains the calcualtion.

I know that the code to set up the vlookup calcualtion is;

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Sheet2!R2C1:R138C4,2,FALSE)"

but this only works for a fixed range of Sheet2!R2C1:R138C4

So far I've got...

Sheets("Sheet2&quot.Range("a2&quot.Select
Selection.CurrentRegion.Select

...to select my varying range

What I need to know is how to set this range as a variable that I can then insert into my line of code containing the calcualtion.

Thanks,

 

[SkipVought]

Zukkster,

You ought to get familiar with the OFFSET worksheet function. A Table like yours can be defined by a dynamic named range using Insert/Name/Define -- entering a Name in the Names in Workbook textbox and entering a formula like this in the RefersTo textbox...

=offset(Sheet1!$A$2,0,0,counta(Sheet1!$A:$A)-1,counta(Sheet1!1:1))

Check HELP on this function to gain a fuller understanding.

Then use this name in your lookup expression

Skip,

http://www.TheOfficeExperts.com

 

[combo]

You can build formula string:
[tt]s = Worksheets(2).Range("A2&quot.CurrentRegion.Address(External:=True, ReferenceStyle:=xlR1C1)
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1," & s & ",2,FALSE)"[/tt]

combo