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:

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

    Any help would be appreciated.


  • 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:

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

    If you want the formula then use this:

    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!