Range Definition in Macro ?

[Guest]

Hi,
Take this formula below...
VLOOKUP($F2,Calculations!$C$2:$C$25000,3,FALSE))

Now, I want "$25000" to be defined as a Range in a Macro. I have my raw data
varying from 1000 to 30000 everyday. If use this above formula, Excel
calculates for a long long time.

Instead I want to define a range in the macro and use that Range Name in
this formula.
One more thing, Range should have only the Row number (ex: 25000) and not
the column index (A, B...)

Ideally I want my formula to look like...
VLOOKUP($F2,Calculations!$A$2:$C RangeName,3,FALSE))

I know this syntax is wrong...

Is there a method?

 

[Rowan]

Try:

Dim eRow As Long
eRow = Sheets("Calculations").Cells(Rows.Count, 3).End(xlUp).Row
ActiveCell.Formula = "=VLOOKUP(F2,Calculations!$A$2:$C$" _
& eRow & ",3,FALSE)"


Hope this helps
Rowan