VBA Modifying Formulas - Inserting @

[alexvilla00]

Hello, I'm doing an excel with macros and I've encountered an error I can't solve.

When assigning a formula in VBA like this:

cb.Formula = "=FILTER(Eq_Panto[Nombre Desplegable], Eq_Panto[VAlim]='Configurador Civity LF'!TensionAlim)"

It seems to be working as intended in terms of not giving error when assigning the formula. The only big problem is that, for some reason, it adds an "@" at the beginning of the formula, so when you go and check the formula on the cell it appears like this:

=@FILTRAR(Eq_Panto[Nombre Desplegable]; Eq_Panto[VAlim]='Configurador Civity LF'!TensionAlim)

That "@" makes the function not work properly. When there's no "@" it works jsut fine. Otherwise, it only gives one of all results of the filter.

Any idea anyone??

Thanks

[#2]

Hi @alexvilla00,

In Excel, when you see an "@" symbol at the beginning of a formula, it means the formula is treated as an array formula. This can sometimes affect how the formula behaves.

In your situation, if you want to remove the "@" symbol from the formula assigned via VBA, you can use the .FormulaArray property instead of the .Formula property. This change should help your formula work as expected without the "@" symbol causing any issues. So, use the following VBA code:

cb.FormulaArray = "=FILTER(Eq_Panto[Nombre Desplegable], Eq_Panto[VAlim]='Configurador Civity LF'!TensionAlim)"

This way, you should be able to assign the formula without the "@" symbol, ensuring it functions as intended.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

[#3]

@LeonPavesic now it's showing 

{=FILTRAR(Eq_Panto[Nombre Desplegable]; Eq_Panto[VAlim]='Configurador Civity LF'!TensionAlim)}

 And not working properly either.

Any other ideas?

[#4]

@alexvilla00 

Please uncheck this option:

[#5]

But that is configuration from the app, right? Is there a way of configuring that when opening my workbook? I need to use this excel using various PCs and I don't want to configure that everytime

Nevermind, that doesn't work anyway 😞

[#6]

It works for me without issue. It might be worth trying to close Excel completely, open the workbook, and try again.

[#7 · Accepted solution]

@Patrick2788 I've tried but for me it's not working, maybe it's because I'm using dynamic tables, I don't know...
Either way, I've found that using "formula2" instead of "formula" works just fine.

 Thanks to all of you 😄