Previous Topic Next Topic

VLOOKUP table_array parameter as a variable

Solved/Closed
NotSoEXCELlent - 2 Aug 2011 à 19:38
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 3 Aug 2011 à 13:41
Hello,





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.

I can't get it to work. Please help.

1 response

Réponse 1 / 1
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
3 Aug 2011 à 05:55
You mean some thing like this

lastrow = cells(rows.count, "A").end(xlup).row
lastcol =cells(1, columns.count).end(xltoleft).column
src = "R2C1:R" & lastrow & "C" & lastcol
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],Position!" & src & ",2,FALSE)"
NotSoEXCELlent
3 Aug 2011 à 10:53
That worked. Thanks!
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
3 Aug 2011 à 11:08
How could it work :) there was a bug in the code. Glad that you were able to fix it and make it work
NotSoEXCELlent
3 Aug 2011 à 11:22
there was? this is what i did and it worked fine..
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)"
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
3 Aug 2011 à 13:41
In the earlier code, I had "=VLOOKUP(RC[-10],Position!" & srs & ",2,FALSE)"
but the variable should have been (as you correctly used) src

Similar discussions

How to invoke VLookUp function in Excel VBA.
Wingie -
TrowaD -

1 response
Removing "FALSE" from an IF statement with a VLOOKUP
RayRay -
RayRay -

2 responses
Comparing columns and returning value
ll -
kittu -

35 responses
How to store cell value in a variable excel vba OR Increase Cell Value in Excel
Raj_1562 -
vcoolio -

1 response
Compare values in two columns and return the value from third
ramdubai -
keshav -

11 responses