tom88Excel
New Member
- Joined
- Sep 29, 2014
- Messages
- 30
Hi all,
Can you please guide me through this problem? I have 2 formulas below no.1 & no.2. No.1 does NOT work. It pops up a Run-time error 1004 - unable to get the VLookup property of the WorksheetFunction class. However, no. 2 works fine. I tested my variable name "Lookup_Sheet_Range" below, it does returns Range("SHEET21_INV") just like no.2 below. So I really don't know what I'm missing...
Can you please show me how I can make no. 1 to work? I need to be able change variables constantly. Thank you!!!!
Can you please guide me through this problem? I have 2 formulas below no.1 & no.2. No.1 does NOT work. It pops up a Run-time error 1004 - unable to get the VLookup property of the WorksheetFunction class. However, no. 2 works fine. I tested my variable name "Lookup_Sheet_Range" below, it does returns Range("SHEET21_INV") just like no.2 below. So I really don't know what I'm missing...
Can you please show me how I can make no. 1 to work? I need to be able change variables constantly. Thank you!!!!
Code:
Sub Test3()
Dim Invoice_No As String
Dim Lookup_Sheet_Name As String
Dim Lookup_Sheet_Range As String
Dim Answer As String
Invoice_No = Range("D" & (ActiveCell.Row)).value
Lookup_Sheet_Name = Range("I" & (ActiveCell.Row)).value
Lookup_Sheet_Range = "Range (" & Chr(34) & Lookup_Sheet_Name & Chr(34) & ")"
MsgBox Invoice_No
MsgBox Lookup_Sheet_Name
MsgBox Lookup_Sheet_Range
Answer = Application.WorksheetFunction.VLookup(Invoice_No, Lookup_Sheet_Range, 4, False) ' # 1 This does not work
Answer = Application.WorksheetFunction.VLookup(Invoice_No, Range("SHEET21_INV"), 4, False) ' # 2 This works fine
MsgBox Answer
End Sub