Unable to get range variable to work in Vlookup formula

Hello, my goal is to write a macro that will do a Vlookup on indefinite interval, for both data tested and range of Vlookup. When I run my Macro it selects the range, but it doesn't use it in Vlookup, so it returns me #Name error. Also I bet there is other ways to do that I'm trying to apply this to more complex problems, so I want to keep it as simple as possible.

Here is my Code so far:

Please Login or Register  to view this content.

I was thinking maybe If I dim rg as something else but range it might work, Also I've tried just putting rg same problem, any help is appreciated.

Thank you

Hello ZmeY,

Welcome to the Forum!

The problem with the formula is it expects a cell address (string) and not the cell (range object). The code below will add the relative address of the selection into the formula.

Please Login or Register  to view this content.

Hello ZmeY,

The formula shows the address in single quotes? Unless this is some strange feature of Excel 2010 I don't know about, I have no explanation for that. Can you post the workbook for review?

Hello ZmeY,

I feel like an idiot. Your formula is using R1C1 referencing. The address being added is an A1 reference. The two styles can not be used in the together in the same formula. Here is the working code.

Please Login or Register  to view this content.