0
Thanks

A few words of thanks would be greatly appreciated.

Excel - VLOOKUP table_array parameter as a variable




Issue


I have this piece of code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],Position!R2C1:R50C2,2,FALSE)" 


However, I want to make Position!R2C1:R50C2 a variable. Meaning, this range may not always be the same row and column. I want it to be something like a variable range (ie, 'Range(Range("A2").End(xlDown), Range("A2").End(xlToRight))' for Position worksheet.

But it isn't working.

Solution


Dim src As String  
Dim ws As Worksheet  
Set ws = Sheets("Position")  

lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row  
lastColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column  
src = "R1C1:R" & lastRow & "C" & lastColumn  

ActiveCell.FormulaR1C1 = _  
"=VLOOKUP(RC[-10],Position!" & src & ",2,FALSE)"


Thanks to rizvisa1 for this tip.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - VLOOKUP table_array parameter as a variable », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

0 Comments