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
My documents environment variable - Guide
Parse error: syntax error, unexpected variable - Guide
Transfer data from one excel worksheet to another automatically vlookup - Guide
Sharepoint link to Excel via a cell variable ✓ - Excel Forum
Vlookup to find missing data in 2 columns - Excel 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

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

Error to open Check the temp environment vari

Conditional Formatting in a vlookup

Compare values in two columns and return the value from third

Concanating text using vlookup forumula

Similar discussions

Subscribe To Our Newsletter!