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?

 

[Micron]

Maybe ?

For i = 1 To UsedRange.Rows.Count
Range("D" & i).Formula = ...
Next

 

[eastrand]

With this being an XLookup function, it should spill into all rows with data and you shouldn't need to copy into all the rows. When VBA writes the formula into D2, does it add any @ symbols anywhere in the formula?

 

[Fluff]

You can just use

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

 

[evenyougreg]

With this being an XLookup function, it should spill into all rows with data and you shouldn't need to copy into all the rows. When VBA writes the formula into D2, does it add any @ symbols anywhere in the formula?

Yes, outside of VBA and just running it in D2 it spills down just like you said. If I use the VBA code I have it looks like this afterwards:

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

 

[eastrand]

Try what @Fluff suggested. If that doesn't work for some reason, you can also do a replace for the @ if there are any.

    Sheets("Presc_By_Prov").Range("D:D").Select
    Cells.Replace What:="@", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
        FormulaVersion:=xlReplaceFormula2

 

[Fluff]

No need to replace the @ sign, using formula2 rather than formula will do the trick.

 

[eastrand]

I love learning new things. Thanks @Fluff.

 

[evenyougreg]

Maybe ?

For i = 1 To UsedRange.Rows.Count
Range("D" & i).Formula = ...
Next

Thanks for your assistance, this is what I got:

Runtime error 424, Object required.

Sub test()

    For i = 1 To UsedRange.Rows.Count
    Range("D" & i).Formula = "=XLOOKUP(INDIRECT(""A2:A"" & COUNTA(A:A)),VMs!A:A,VMs!B:B,""Not Found"")"
    Next

End Sub

 

[evenyougreg]

You can just use

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

This worked! Thank you again @Fluff