Forum Discussion

alexvilla00's avatar
alexvilla00
Copper Contributor
Oct 06, 2023
Solved

VBA Modifying Formulas - Inserting @

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

excel
Formulas and Functions
Macros and VBA
  • alexvilla00's avatar
    alexvilla00
    Oct 06, 2023

    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 😄

6 Replies

  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor
    Oct 06, 2023

    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)

    • alexvilla00's avatar
      alexvilla00
      Copper Contributor
      Oct 06, 2023

      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?

Resources