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:
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:
Why does my range variable not work?
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?