variable within vlookup

[JJules01]

Hi All,

This is really frustrating me: Trying to set a current region within a vlookup. Never done this before. Data in "test 1" sheet is somewhat dynamic: rows are variable; columns are set to 12 (A:L). Thought CurrentRegion would do the trick. And maybe it still could but referencing it into my vlookup errors out. is my syntax for the vlookup incorrect or I bombed the whole code? thanks

Set test_data = Sheets("test1").Range("A1").CurrentRegion
Sheets("Test2").Range("T2").Formula = "=VLOOKUP($F2,test_data,4,false)"

 

[xenou]

Hi,
You're creating a normal formula the same way you'd type it into a cell. So you need a range address or a defined name in there:

Sheets("Test2").Range("T2").Formula = "=VLOOKUP($F2," & test_data.address & ",4,false)"

Basically, you're building a formula string using concatenation.
ξ

 

[JJules01]

Hi Xenou,

Appreciated helping a newbie here. Couple of things I dont understand (yet):

- I already defined test_data as the currentregion on sheet 'test1'. why can't I just ref it as the lookup array?
- what is the significance of **.address you provided above. i used it as you showed and returned a strange range of (A1:D3) from the same sheet 'test2'.

Sorry in advance if my questions sound stupid...

 

[xenou]

Hi,

When you do this:

Set test_data = Sheets("test1").Range("A1").CurrentRegion

you create a reference variable called test_data. You can use it in vba (no problems there). But what you're doing with vba is not pure. You are using vba to write a formula in a cell on the worksheet. When you write formulas in a worksheet you don't use vba variables in the formulas. So that's why it won't work that way. You have to write a formula that works in a worksheet cell as a regular formula, without any vba variables in it.

If you wanted test_data to be a defined name then that's another story (I'm not sure if you want to do that).

i used it as you showed and returned a strange range of (A1:D3) from the same sheet 'test2'.

This shouldn't be a strange range. It should be the address of the current current region of cell A1, which is what you set range test_data (in your macro) to be. I see you have sheet test1 in the code and sheet test2 in this comment. Are you using more than one sheet?

By the way, it might be simpler to use a dynamic named range (no vba involved):
http://www.contextures.com/xlNames01.html

 

[JJules01]

thanks Xenou,

I am using two sheets: test1 and test2. formula resides in 'Test2'.range ("t2") but it looks up a range from 'test1'.range("A:L") to return my value. I tried to define 'test_data' as a variable Current Region on sheet 'test1'. If I can't use a variable in my vlookup formula, then that's a showstopper for me. Oh, well... learning every day.