Hi Tony,

"=VLOOKUP(RC[1],'" & LastMonth & "'!C[1]:C[67],64,FALSE)"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Tony" wrote in message
...
I have a report that runs each month and it places a new sheet in the file
with all the info I need. Another sheet is used for trendind the results
of
the monthly sheets. I have the VLOOKUP function working properly (thanks
to
all of you) but the problems is that it will work ok this month but in
future
months it will not use column A as the Lookup value. Here is what I have

Worksheets("Trend").Activate
Range("A1").Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = LastMonth & " Rank"
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-11],'" & LastMonth & "'!C[-11]:C[67],64,FALSE)"
ActiveCell.Offset(0, -1).Activate
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Activate
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown

The Vlookup is looking back 11 columns but I need it to always look to
column A. The same for the 'Table Array' section. It needs to include
column
A. Any help is greatly aprreciated.