using Range variable in VBA VLookup

Special K

Board Regular
Joined
Jun 20, 2011
Messages
60
Hello,

I can't seem to get a range variable to work as the table_array argument to the VBA Vlookup function. Here is what I have:

Code:
On Error GoTo MyErrorHandler:

Dim new_data_table As Range
Dim user_ID As Variant
Dim last_row As Long
Dim last_col As Long

last_row = Sheets("new_data").Cells(Rows.Count, 1).End(xlUp).Row
last_col = Sheets("new_data").Range("A1").End(xlToRight).Column

Set new_data_table = Range(Sheets("new_data").Cells(2, 1).Address, Sheets("new_data").Cells(last_row, last_col).Address)

user_ID = Application.VLookup("user1", new_data_table, 2, False)

MyErrorHandler:
If Err.Number = 1004 Then
  MsgBox "User Not Present in the table."
ElseIf Err.Number = 13 Then
  MsgBox "You have entered an invalid value."
End If

Running the above always produces a "You have entered an invalid value" error. Why? If I use a hard-coded range for the VLookup, it works fine:

Code:
user_ID = Application.VLookup("user1", Sheets("new_data").Range("A2:B2"), 2, False)

Why does my range variable not work?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
Set new_data_table = Range(Sheets("new_data").Cells(2, 1), Sheets("new_data").Cells(last_row, last_col))
using .address means that the new_data_table range will be on the activesheet.
 

Special K

Board Regular
Joined
Jun 20, 2011
Messages
60
Try
Code:
Set new_data_table = Range(Sheets("new_data").Cells(2, 1), Sheets("new_data").Cells(last_row, last_col))
using .address means that the new_data_table range will be on the activesheet.

I tried that but it gave the following error:

Method 'Range' of object '_Worksheet' failed
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows
Not sure why, it worked for me.
What if you try
Code:
Set new_data_table = Sheets("new_data").Range(Sheets("new_data").Cells(2, 1).Address, Sheets("new_data").Cells(last_row, last_col).Address)
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,226
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Not sure why, it worked for me.
What if you try
Code:
Set new_data_table = Sheets("new_data").Range(Sheets("new_data").Cells(2, 1).Address, Sheets("new_data").Cells(last_row, last_col).Address)
As you already pointed out Fluff, using the address version will cause a runtime error on this line:

user_ID = Application.VLookup("user1", new_data_table, 2, False)

if the line is executed when the sheet "new_data" is not the active sheet, but there's no need for the .Address when specifying the range. The version from post #2 works fine for me regardless of what sheet is active when the lookup is executed.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows
Both versions work for me regardless of which sheet is active.
But when using .Address you need to specify the worksheet for the range, which the OP hadn't done.
 

Special K

Board Regular
Joined
Jun 20, 2011
Messages
60

ADVERTISEMENT

I tried that but it gave the following error:

Method 'Range' of object '_Worksheet' failed

I tried it again as follows (note the addition of "Application." at the front:

Code:
Set new_data_table = Application.Range(Sheets("new_data").Cells(2, 1), Sheets("new_data").Cells(last_row, last_col))

I am not sure why the "Application." is required in this case.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If your code is in a different worksheet code module, you’d need to use either Application.Range or Sheets(“new_data”).Range

Note though that application.vlookup does not cause a runtime error, it returns an error value which you should test with IsError.
 
Last edited:

Special K

Board Regular
Joined
Jun 20, 2011
Messages
60
If your code is in a different worksheet code module, you’d need to use either Application.Range or Sheets(“new_data”).Range

Note though that application.vlookup does not cause a runtime error, it returns an error value which you should test with IsError.

You are correct. I was also able to get my code to work by using the following:

Code:
Set new_data_table = Sheets("new_data").Range(Sheets("new_data").Cells(2, 1), Sheets("new_data").Cells(last_row, last_col))

When you say "If your code is in a different worksheet code module" - different from what? Do you mean different from the new_data sheet/module? All of my code (not all shown in my first post) is on a sheet other than "new_data", and is activated from buttons on that sheet. Is that why I can't just use the Range operator when referring to cells on the new_data sheet?

Is there any difference between using Application.Range and Sheets("new_data").Range?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yes, a simple Range statement is equivalent to Me.Range in a worksheet code module.

I prefer to use the Sheets version rather than Application so that I can use a With block:

Code:
with ws
 .Range(.Cells(),.Cells())
End with
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,146
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top