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

 

[tom88Excel]

Thank you for showing me this. Not sure if i'm doing it right. I got a 2 on the Immediate screen... By reading JumpingCrab's post, I've got it resolved by changing Application.WorksheetFunction.Vlookup to Application.VLoopkup. Thank you for you help!!!

 

[tom88Excel]

The functions
Application.WorksheetFunction.VLookup and
Application.VLookup
handle errors in different ways.

Hey JumpingCrab, thank you for your reply. I have got it all working fine now. However, I'm still trying to understanding these codes below... I have read more article about it but still don't quite understand it.. I would appreciate if you (or someone) can explain to me or correct me about what these codes means...

    On Error Resume Next 'I'm guessing this code avoids error messages from popping up correct?
    Answer = Application.WorksheetFunction.VLookup(vlookupvalue, VlookupRange, 2, False)
    If Err.Number <> 0 Then' I don't understand why it is <> 0 but not = 0
        MsgBox "not found"
        Err.Clear
    Else
        MsgBox Answer
    End If
    On Error GoTo 0 'Where does this go to? I don't get this part...

 

[JumpingCrab]

No problem.

The default VBA run-time behavior is to stop once an error occurs, show a dialog box with a (typically somewhat cryptic) message and present you with the options to either stop or debug.
This default is the "on error goto 0" behavior.
By changing this to "On Error Resume Next", VBA will catch the error, but just continue on the next line. This allows you to check what the error was.

A Err.Number <> 0 means there is an error;
Err.Number=0 means there was no error.

You check for an error <>0, because even if the code does not generate a runtime error, the "If Err.Number <> 0 Then" statement is still executed so you'll want to skip the error handling it if there is no error
Once the error has been handled, you return to default behavior by setting "On Error GoTo 0" again.

A detailed explanation of various VBA error handling techniques can be found here.

Cheers,
JL

 

[tom88Excel]

No problem.
...
A detailed explanation of various VBA error handling techniques can be found here.
Cheers,
JL

JumpingCrab, Thanks a bunch. Your explanation is very clear and easier to understand than the articles that I read. Thank you for your link!

 

[JumpingCrab]

You're welcome