Run formula in VBA until last row

[evenyougreg]

I'm trying to get this formula to run from D2 until the last detected row, and without using hard coded numbers like D2 until D1243 or whatever because the dataset changes all the time. I've had issues in the past with this on another forum so I will try my luck here.

the formula is

=XLOOKUP(INDIRECT("A2:A" & COUNTA(A:A)),VMs!A:A,VMs!B:B,"Not Found")

and the VBA code I have only populates D2, but would like it to run until the last row...

Range("D2").Formula = "=XLOOKUP(INDIRECT(""A2:A"" & COUNTA(A:A)),VMs!A:A,VMs!B:B,""Not Found"")"

Any thoughts?

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Micron]

Sorry, I only gave you the related part, figuring you'd add the rest, including declaring i As Integer. Plus, it has to be code behind the sheet, not the workbook. So no idea where you put it thus don't know what the missing object is. Perhaps a sheet reference? Aside from that, I must have misunderstood the problem because I don't see how that helps when part of the range is hard coded (the 2 in A2) which is what I thought you didn't want. Glad you got it solved anyway.