Excel updating formula set in VBA to alter table column references [duplicate]

I am attempting to alter the formula of a cell using VBA, and that formula references a column in a table in another worksheet. However, when I run my code, Excel appears to autocorrect the formula to include an "@" symbol that alters the meaning of my formula.

Here is an illustrative snippet of VBA code:

Range("C2").Select
Selection.Formula = "=IFERROR(INDEX(standardised_variable_names[standard_variable_name], MATCH(1, ([@[original_variable_name]] = standardised_variable_names[original_variable_name])*([@[fin_stat]] = standardised_variable_names[fin_stat]), 0)),"" - "")"

But this is what I get in the cell after running it (note the extra "@" symbol and associated square brackets). This changes the meaning from referencing the whole column of my table.

=IFERROR(INDEX(standardised_variable_names[standard_variable_name], MATCH(1, ([@[original_variable_name]] = standardised_variable_names[@[original_variable_name]])*([@[fin_stat]] = standardised_variable_names[@[fin_stat]]), 0)), " - ")

Any ideas how to stop this autocorrection? I've attempted the below at the top of my code but it appears not to work.

Application.EnableAutoComplete = False


Read more here: https://stackoverflow.com/questions/64940496/excel-updating-formula-set-in-vba-to-alter-table-column-references

Content Attribution

This content was originally published by Luke Wheeler at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: