[nquadr]

Hi I was doing some research to call a vlookup function in VBA, but the syntax I am using is not correct. Can you help direct me in the correct syntax, this is what I have so far. I am new to VBA.

Background:

I am dong a vlookup between two excel workbook, hence the names wbOld and wbNew.

I am not sure if the object, property, and method are in the correct format to execute the function.

    wbOld.ActiveSheet.Cells(xOld, 21) = wbOld.Application.WorksheetFunction.VLookup(Cells(xOld, 19), wbNew.ActiveSheet.Range("S:T"), 2, False)

Thanks for your help.

This post has been edited by nquadr: 12 June 2012 - 05:27 AM

[BobRodes]

Before I respond further, let me refer you to my post number 7 in your previous thread, which has to do with how to explain your problems. Please get in the habit of explaining your problems properly (why do I have to ask more than once?).

[nquadr]

BobRodes, on 12 June 2012 - 07:57 AM, said:

Before I respond further, let me refer you to my post number 7 in your previous thread, which has to do with how to explain your problems. Please get in the habit of explaining your problems properly (why do I have to ask more than once?).

Sorry, I was under the impression I was explaining it clearer this time. But I'll try again.

I am having trouble with my VBA Lookup function. I have created 2 workbooks which I have instantiated to wbOld and wbNew. I am taking a column from wbOld and comparing with a range in wbNew. I want it to return the second column, with an exact match. Unfortunately, it is not working. I believe it to be a problem with my syntax.

Revised Code

Sub Comparison()

    Dim xOld As Integer
    xOld = 2
             
    
    wbOld.Application.ActiveSheet.Cells(xOld, 21) = wbOld.Application.WorksheetFunction.VLookup(Cells(xOld, 19), wbNew.Application.ActiveSheet.Range("S:T"), 2, 0)
        
        

End Sub

The error I am getting is:

Run-time error '1004'
Unable to get the VLookup property of the WorksheetFunction class.

Hope this is better.

Eventually, I will get it to loop. If I can get it to work. Your help would be greatly appreciated. Thanks

[BobRodes]

Yes, it's better. It tells what the error is, and what line it's on. (Well, at least I can easily figure that part out.) If you read the post I mentioned, you would see that you need to tell the error and the line on which it occurred. (If you were going to a friend's apartment to buy his old XBox games, would you rather have him say "I live at Cherry Lane apartments" or would you rather have him say "I live at Cherry Lane Apartments, Apt ZX-59"? How motivated would you be to show up at the office and try and track down his apartment number? Hope this analogy explains.)

Have you tried googling the error string? I see a lot of people with the same problem.

[nquadr]

BobRodes, on 12 June 2012 - 01:50 PM, said:

Yes, it's better. It tells what the error is, and what line it's on. (Well, at least I can easily figure that part out.) If you read the post I mentioned, you would see that you need to tell the error and the line on which it occurred. (If you were going to a friend's apartment to buy his old XBox games, would you rather have him say "I live at Cherry Lane apartments" or would you rather have him say "I live at Cherry Lane Apartments, Apt ZX-59"? How motivated would you be to show up at the office and try and track down his apartment number? Hope this analogy explains.)

Have you tried googling the error string? I see a lot of people with the same problem.

Yes, I have googled a lot for the error, that is how I came up with my syntax, what I can't really find is the correct way to code for a function that references two different workbooks in the same vba formula, so any help in correcting the syntax would be a great help. Thanks.

[BobRodes]

I thought we had resolved that earlier?

Edit: have a look at this. The answerer knows Excel better than I do.

This post has been edited by BobRodes: 13 June 2012 - 08:35 AM