VLookup

Hi Freddie,

Thank you for the forum question.

What you want you can record with the macro recorder. Start the recorder and do what you want and you will have the code in the visual basic editor. Just record it for the first record and put it inside the loop.

I did it with one of my workbooks and got this:

Sheets("lists").Select
Range("A5").Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],lists2!R3C2:R22C2,1,0)"

and then I added the loop and ended like this:

Sub Macro1()

Sheets("lists").Select
Range("A5").Select
Do Until ActiveCell.Offset(0, -1) = ""

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],lists2!R3C2:R22C2,1,0)"
ActiveCell.Offset(1, 0).Select
Loop

End Sub

I hope that this can help you.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Hi Freddie,

Thank you for your question.

Please have a look at the following code where I've made some changes. I've streamlined it but more importantly, I would suggest giving your data table on the LBARATEF sheet a range name. This helps to tidy up your code. In my example I used "rangename" (see below). Also, in your vlookup formula there is a speechmark in the offset brackets, which you need to delete.

Sheets("LBAPARIF").Range("L2").Select
Do Until ActiveCell.Offset(0, -1) = ""
ActiveCell = WorksheetFunction.VLookup(Activecell.Offset(0,-1), _
Range("rangename"), 2, 0)
ActiveCell.Offset(1, 0).Select
Loop

I hope this helps.

Kind regards
Marius Barnard
Excel Trainer