[EDIT] Ok so the solution is to use Formula2 or Formula2R1C1 instead of Formula or FormulaR1C1; just follow the link below in the third comment to understand better. I'll leave my question here because I've spent a long time searching on MS docs, StackOverflow and Google in general, but found nothing... really a tricky issue to solve when you don't know Microsoft's "Implicit Intersection" keyword, because "@" is not a very search-friendly character. Hopefully my question here will help people get to JvdV's clear answer.
This really is a messed up way to handle things, always patching up instead of using a clean theoretical basis, but just business as usual for MS.
Excel now adds "@" sign in formulas automatically, and this makes my VBA-generated formulas wrong. How can I disable this?
For instance, when I reference a whole column with a test such as A2 = C:C, Excel normally checks for each row in column C whether A2 is equal to the row's value. However, when Excel decides to add a @ sign such as A2 = @C:C then it only checks whether A2 = C2 and my formula does not work anymore.
I read in some places that the @ sign replaces the ThisRow keyword for Structured tables, but this does not help me much in knowing how to override it.
The issue is that my formula is generated in VBA, and the @ sign isn't added until after the VBA code terminates... so I cannot use a Replace(myFormula, "@", ""). Even if I force the range's formula calculation in VBA and overwrite formula with its value, it does not work.
Any ideas?
One more reason to avoid Microsoft...ughhhhh...I wish I could have used GSheets for this tool.
