How to properly add column to the Excel table and insert a formula using VBA-CodePudding

I would like to be able to add column and insert formula thats looks up for values from a pivot table. 'Peer Group Name' is one of the table column. So far, I'm doing this:

Set wrk = ThisWorkbook
Set trg_sht = wrk.Worksheets("Peers & Instruments - Overview")
Set tbl = trg_sht.ListObjects("Peers_Instruments_Values")

' Add few additional columns on position 26 & 27
tbl.ListColumns.Add(26).Name = "Median 1Y Return"
tbl.ListColumns.Add(27).Name = "Max 1Y Return"

tbl.ListColumns("Median 1Y Return").DataBodyRange.FormulaR1C1 = "=VLOOKUP([@[Peer Group Name]]," & "'" & CStr(wrk.Worksheets("Peer Group Stats") & "'" & "!C[-25]:C[-22],3,0)")

So the two columns are being added, but the formula doesn't. Any idea why? Is it something do to with wrong column reference?

CodePudding user response：

This looks off:

CStr(wrk.Worksheets("Peer Group Stats") & "'" & "!C[-25]:C[-22],3,0)")

You don't need a reference to the worksheet, nor CStr. You just need the worksheet name, which can be hard-coded:

tbl.ListColumns("Median 1Y Return").DataBodyRange.FormulaR1C1 = "=VLOOKUP([@[Peer Group Name]],'Peer Group Stats'!C[-25]:C[-22],3,0)"