Vlookup in VBA

[tom88Excel]

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

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

 

[Anthony47]

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

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:

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]

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:

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]

Thank you Anthony47, your code works for me.

 

[tom88Excel]

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]

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:

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:

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

 

[Anthony47]

He he, you tested different options but ignored my suggestion:

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

Give it a try...

 

[tom88Excel]

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

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]

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.

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

 

[JumpingCrab]

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.

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