using Range variable in VBA VLookup

[Special K]

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:

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:

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

Why does my range variable not work?

 

[Fluff]

Try

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]

Try

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]

Not sure why, it worked for me.
What if you try

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]

Not sure why, it worked for me.
What if you try

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]

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]

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:

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]

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.

 

[Special K]

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:

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]

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:

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