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!!!!

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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,972
Your Lookup_Sheet_Range is a string, wereas VLOOKUP requires a Range as second parametre.
Assuming that the variable Lookup_Sheet_Name contains not a sheet name but a "named range" (congratulation for trying to fool your competitors) you should use
Code:
Answer = Application.WorksheetFunction.VLookup(Invoice_No, Range(Lookup_Sheet_Name), 4, False)

Keep in mind that if the searched value is not found in the target range, that instruction will rise an error 1004; therefore I recommend the following version:
Code:
Dim Answer as Variant            '<<< Change the "Answer" Declaration
'
Answer = Application.VLookup(Invoice_No, Range(Lookup_Sheet_Name), 4, False)
If IsError(Answer) Then
    'CODE FOR Invoice_No NOT FOUND
Else
    'CODE FOR INVOICE_No FOUND
End If
Bye
 

JumpingCrab

Board Regular
Joined
Dec 27, 2017
Messages
96
Office Version
  1. 2019
Platform
  1. Windows
The Vlookup function requires a range object in the second position.
You declare Lookup_Sheet_Range as a string however.
It is not clear what you are trying to accomplish with the macro. Do you have named ranges on several worksheets that you need to lookup data from?

If that is the case, you could do something like this:

Code:
Public Sub Test1()
    Dim ws As Worksheet
    Dim VlookupRange As Range
    Dim VlookupRangeName As String
    Dim vlookupvalue As Variant
    
    Set ws = ActiveSheet
    VlookupRangeName = "NamedRange1"
    Set VlookupRange = ws.Range(VlookupRangeName)
    vlookupvalue = 2
    
    Answer = Application.WorksheetFunction.VLookup(vlookupvalue, VlookupRange, 2, False)

End Sub

This approach will allow you to change the the name of the lookup range in your code to match the named range in the sheet.

Hope this helps you out.
 

tom88Excel

New Member
Joined
Sep 29, 2014
Messages
30

ADVERTISEMENT

Thank you JumpingCrab! I will keep your solution handy. yes, I have named ranges on many different worksheets that I need to lookup data from. Thanks.
 

tom88Excel

New Member
Joined
Sep 29, 2014
Messages
30
Keep in mind that if the searched value is not found in the target range, that instruction will rise an error 1004

Thank you for your advise. However, I'm still unable to make the ISERROR code working. I tested it by deleting the invoice no on my data range, but I still get this 1004 error.

I tried this:
Code:
Dim Lookup_Sheet_Name As String
Dim Answer As Variant


Invoice_No = Range("D" & (ActiveCell.Row)).Value
Lookup_Sheet_Name = Range("I" & (ActiveCell.Row)).Value

Answer = Application.WorksheetFunction.VLookup("123456-2", Range(Lookup_Sheet_Name), 4, False)' 1004 error occur here.



If IsError(Answer) Then
    'CODE FOR Invoice_No NOT FOUND
    MsgBox ("Invoice No. Not found")
Else
    'CODE FOR INVOICE_No FOUND
    MsgBox Answer
End If

I also tried this, it still failed:
Code:
Dim Lookup_Sheet_Name As String
Dim Answer As Variant


Invoice_No = Range("D" & (ActiveCell.Row)).Value
Lookup_Sheet_Name = Range("I" & (ActiveCell.Row)).Value



If IsError(Application.WorksheetFunction.VLookup("123456-2", Range(Lookup_Sheet_Name), 4, False)) Then' 1004 error occur here
    'CODE FOR Invoice_No NOT FOUND
    MsgBox ("Invoice No. Not found")
Else
    'CODE FOR INVOICE_No FOUND
    Answer = Application.WorksheetFunction.VLookup("123456-2", Range(Lookup_Sheet_Name), 4, False)
End If

Is there something else I'm missing? the error only occur when the lookup value is not found...
 
Last edited:

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,972

ADVERTISEMENT

He he, you tested different options but ignored my suggestion:
Anthoy said:
Code:
Answer = Application.VLookup(Invoice_No, Range(Lookup_Sheet_Name), 4, False)

:LOL:
Give it a try...
 

tom88Excel

New Member
Joined
Sep 29, 2014
Messages
30
Your code worked perfectly. It was the ISERROR code that I'm having trouble with. When the vlookup value exist, your code works fine, but when there is no lookup value (or when it does not exist), it will still pop up the 1004 error even though I have the following

Code:
Answer = Application.VLookup(Invoice_No, Range(Lookup_Sheet_Name), 4, False)

If IsError(Answer) Then
    Msgbox ("Invoice does not exist")
Else
    Msgbox Answer
End If
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,972
Which line is highligthed when you get the error?
Add the following debug lines, when you get the error and enter the Debug condition, press Contr-g to open the "Immediate" vba window and copy in your next message what you read there.
Code:
Debug.Print 1, Range(Lookup_Sheet_Name).Address         'ADD for debugging
Answer = Application.VLookup(Invoice_No, Range(Lookup_Sheet_Name), 4, False)
Debug.Print 2, Answer                                   'ADD for debugging


Bye
 
Last edited:

JumpingCrab

Board Regular
Joined
Dec 27, 2017
Messages
96
Office Version
  1. 2019
Platform
  1. Windows
The functions
Application.WorksheetFunction.VLookup and
Application.VLookup
handle errors in different ways.

The first one generates a trappable error, while the second one allows you to inspect the answer to verify if an error occurred.
The code below shows both options and how you can handle errors.

Code:
Public Sub Test1()
    Dim ws As Worksheet
    Dim VlookupRange As Range
    Dim VlookupRangeName As String
    Dim vlookupvalue As Variant
    
    Set ws = ActiveSheet
    VlookupRangeName = "NamedRange1"
    Set VlookupRange = ws.Range(VlookupRangeName)
    vlookupvalue = 10
    
    Answer = Application.VLookup(vlookupvalue, VlookupRange, 2, False)
    If IsError(Answer) Then
        MsgBox "not found"
    Else
        MsgBox Answer
    End If

    On Error Resume Next
    Answer = Application.WorksheetFunction.VLookup(vlookupvalue, VlookupRange, 2, False)
    If Err.Number <> 0 Then
        MsgBox "not found"
        Err.Clear
    Else
        MsgBox Answer
    End If
    On Error GoTo 0
End Sub

This same thing applies to the MATCH function. That is where I first bumped into it.
Cheers.
JL
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top