Excel - VLOOKUP table_array parameter as a variable

December 2016




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.

Related :

This document entitled « Excel - VLOOKUP table_array parameter as a variable » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.