Additionally, before you run that code inside a DO loop, please note
that this section of the syntax
ActiveCell.FormulaR1C1 = etc
instructs Excel to generate the VLOOKUP formula in Row 1 Column 1, or
cell A1.

Alternatively, you might copy cell A1, select a desired range, and
paste into that range. The code to copy cell A1 and paste into cells
A6 thru A9 looks like this:
Range("a1").Select
Selection.Copy
Range("a6:a9").Select
ActiveSheet.Paste