Using the VLOOKUP function
 

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.

Using the VLOOKUP function
 

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.

Using the VLOOKUP function
 

That will do the same thing but in the opposite way. By using the RC[1] it
makes it look one column to the right to get the value for the vlookup. In
the original line it used RC[-11] which makes it look 11 columns to the left
for the lookup value. Next month I would need it to look 12 columns to the
left so I can't use a set number, it would have to have the abilitiy to
change. Maybe some way of setting a variable equal to how many columns over
that the active cell is putting the formula in. Then use that variable in the
vlookup formula to look to the left that many columns.....problem is I am not
sure how to set that up.

"Niek Otten" wrote:

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.

Using the VLOOKUP function
 

Hi Tony,

So Sorry,

It should be RC1, without the brackets


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Tony" wrote in message
...
That will do the same thing but in the opposite way. By using the RC[1] it
makes it look one column to the right to get the value for the vlookup. In
the original line it used RC[-11] which makes it look 11 columns to the
left
for the lookup value. Next month I would need it to look 12 columns to the
left so I can't use a set number, it would have to have the abilitiy to
change. Maybe some way of setting a variable equal to how many columns
over
that the active cell is putting the formula in. Then use that variable in
the
vlookup formula to look to the left that many columns.....problem is I am
not
sure how to set that up.

"Niek Otten" wrote:

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.