Unable to get range variable to work in Vlookup formula

**ZmeY** · 01-04-2012, 08:20 PM

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:

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Dim rg As Range

Sheets("Sheet2").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select

    Set rg = Selection

Sheets("Macro Sheet").Select

Range("B1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!rg,2,FALSE)"
    Range("B1").Select
    Selection.Copy
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select

Range(Selection, Selection.End(xlUp)).Select

    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

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

**Leith Ross** · 01-04-2012, 09:15 PM

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.

  ' Change this line 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!rg,2,FALSE)"

  ' To this...
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!" & rg.Address(False, False) & ",2,FALSE)"

**ZmeY** · 01-04-2012, 10:10 PM

Hey Leith, Thank you for your warm welcome. However I've tried the code and it returns same error #Name?. I looked at code in excel and it shows range as 'A1':'B26' (which is my current range) But these semi quotation mess it up. Any other ideas? Appreciate your help.

Thank you

ZmeY

**Leith Ross** · 01-04-2012, 10:21 PM

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?

**ZmeY** · 01-04-2012, 10:33 PM

Hey sure, I believe I've uploaded the file, Just so you know I have 2 other macros there which I use as reference they shouldn't be working properly so skip over those. The one I am testing is Ctrl + Shift + J. Thank you

**Leith Ross** · 01-04-2012, 11:17 PM

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.


Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Dim Addx As String
Dim rg As Range

Sheets("Sheet2").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select

    Set rg = Selection

Sheets("Macro Sheet").Select

Range("B1").Select
     Addx = "R" & rg.row & "C" & rg.Column & ":R" & rg.Rows.Count & "C" & rg.Columns.Count
     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!" & Addx & ",2,FALSE)"
    Range("B1").Select
    Selection.Copy
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select

Range(Selection, Selection.End(xlUp)).Select

    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub