VLOOKUP table_array parameter as a variable

Solved/Closed

NotSoEXCELlent - Aug 2, 2011 at 07:38 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 3, 2011 at 01:41 PM

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.

VLOOKUP table_array parameter as a variable
Parse error: syntax error, unexpected variable - Guide
Transfer data from one excel worksheet to another automatically vlookup - Guide
My documents environment variable - Guide
Parameter incorrect - Hard Drive & SSD Forum
Vlookup if cell contains a word within text ✓ - Word Forum

1 response

Answer 1 / 1

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 3, 2011 at 05:55 AM

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
Aug 3, 2011 at 10:53 AM

That worked. Thanks!

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 3, 2011 at 11:08 AM

How could it work :) there was a bug in the code. Glad that you were able to fix it and make it work

NotSoEXCELlent
Aug 3, 2011 at 11:22 AM

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 January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 3, 2011 at 01:41 PM

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

Comparing columns and returning value

Match partial text string in one sheet to another text string in another sheet

Lookup partial text contained in cell range

Using Vlookup to return insted of 00/01/1900

add/subtract & vlookup formula

Similar discussions

Subscribe To Our Newsletter!