Help with Vlookup VBA

    Hello experts!


    I used macro recorder to try to understand the vlookup function in vba. Can someone tell me how to have that same vlookup function where it looks at the whole column where there is data and stop when it hits the last cell. Also, the results of the vlookup populate in the corresponding cell. I hope this makes sense.


    Here is the code the recorder populated:


    Code
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-9],'Worked Accounts'!RC[-9]:R[998]C,10,FALSE)"
    Range("J3").Select


    Any help would be appreciated.


    Ken

    Re: Help with Vlookup VBA


    I'm not sure what your lookup references are but you can use something like this, which will give you the value:


    Code
    Range("J2") = WorksheetFunction.VLookup(Range("A1"), Sheets("Worked Accounts").Range("B1:K10"), 10, False)


    If you want the formula then use this:

    Code
    Range("J2") = "=Vlookup(A1,'Worked Accounts'!B1:K10,10,False"


    If you give me the formula without the R1C1 references I can give you a better suited formula.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login