Array Populating Table with Formulas; Excel Automatically adding @ Before Table Name

Hi All,

I'm writing a macro that is populating an array with values, some of which are formulas that include table references. The array is then pasting those values/formulas into a destination table. The destination table is based on a source table template that has the formulas populated in cells without the "=" at the beginning as the formulas include lookups referring to other tables in the source template workbook. The source template table and lookup tables are copied into a new workbook so it is necessary the formulas aren't "live" when this happens as these formulas would refer to the source workbook's lookup tables rather than the destination workbook's lookup tables. As the array reads these "formulas", it adds a "=" to the beginning of it to make it live. The array is pasted into the destination workbook's table once it is fully populated, and the formulas are now live in the destination workbook and are referring to the destination workbook's lookup tables.

The issue is that once the array is pasted into the destination table, the formulas' table references are being appended by an "@", causing the entire formula to give a #VALUE error. I have seen people use .Range("A1").Formula2R1C1 when pasting an individual formula, but I am pasting the entire array into the table at one time.

For example, cell A2 in the source workbook's Table1 will contain a "formula":

Formula:

Please Login or Register  to view this content.

The formula itself is not important, hopefully you get the idea - there are references to Table1 and Lookup in it. The macro reads the "formula" into the array and appends it with a "=" (along with a bunch of other hard-coded values that are read as-is).

Formula:

Please Login or Register  to view this content.

Once the array has been populated, it is pasted into the destination workbook's table.

Formula:

Please Login or Register  to view this content.

For some reason, the Table1 references are now appended with an "@".

Formula:

Please Login or Register  to view this content.

Is there any way to prevent this from happening when pasting the array into the table? Or is there some other workaround I can do to prevent this?

So what I ended up doing to solve this issue was convert the SMALL and LARGE portions of these formulas to AGGREGATE formulas and the issue was resolved. I don't know how I would have fixed this otherwise.