SOLVED

Automatically added @ in formulas

Copper Contributor

Hello 

I want to add a formula in my cell:

 

 

=SheetNameFunc()&""-""&ANNEE(AUJOURDHUI())&""-""&MOIS(AUJOURDHUI())

 

 

But each time the formula is added in the cell, excel automatically add @ in front of ANNEE and MOIS. Without the @, my formula works fine, but whenever they are added, it breaks.

 

Do I need to modify my formula or is there a way to not add the @ automatically ?

Thanks

13 Replies

@valentingiaufer 

What does SheetNameFunc return?

The name of the Activesheet where the sub is called

@valentingiaufer 

Thanks. I'm afraid I don't know what causes the problem; I hope that someone else will have a helpful suggestion for you.

Is it the right way to get the year and month or is there an other way that could help ?

@valentingiaufer 

 

As workaround do everything in SheetNameFunc:

Function SheetNameFunc()
    SheetNameFunc = ActiveSheet.Name & "-" & Year(Date) & "-" & Month(Date)
End Function

 

Thanks for your reply but I want it to be dynamic on the sheet so each time I print it, it displays the current date of the print

@valentingiaufer 

I tried this with an Excel Lambda function rather than a VBA UDF and there was no problem.  Then I realised you are probably using an old Excel version and it is that which causes the potential array result from a UDF to be reduced to a single value with "@"?

 

One idea that did occur to me though, is have you tried using TEXTJOIN rather than "&"s?

= JOINDRE.TEXTE("-",,SheetNameFunc(), ANNEE(AUJOURDHUI()), MOIS(AUJOURDHUI()))

 

@Peter Bartholomew 

Thanks for your reply,

I tried to use the joindre method like this:

ThisWorkbook.Sheets(SheetName).Range("H1").Formula = "=JOINDRE.TEXTE(""-"";;""Export"";SheetNameFunc();ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI()))"

But it raises the 1004 error code : Application-defined or object-defined error

Do you think about a solution that can resolve my problem ?

best response confirmed by valentingiaufer (Copper Contributor)
Solution

@valentingiaufer 

The Formula property expects a formula in English. You could use

 

ThisWorkbook.Sheets(SheetName).Range("H1").Formula = "=TEXTJOIN(""-"",,""Export"",SheetNameFunc(),YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

 

To specify it in your local language, use FormulaLocal:

 

ThisWorkbook.Sheets(SheetName).Range("H1").FormulaLocal = "=JOINDRE.TEXTE(""-"";;""Export"";SheetNameFunc();ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI()))

It works !!!!
Thank you so much :)
Have a great day !

I have the same problem, each time I re-opened this file, '@' is automatically added to the below formulas, would you please suggest a solution? Thanks a lot! @HansVogelaar 

 

=TEXTJOIN(" ",TRUE," WRITE-VARS",IF(@H10:H502<>"",G10:G502,""))

 

=IFERROR(@INDEX('Aspen Data In'!D:D,SMALL(IF(@'Aspen Data In'!D$3:D$800<>"",@ROW($3:$800)),ROW('Aspen Data In'!D1))),"")

 

@Tina_Gao 

Sorry, no idea. I cannot reproduce the problem.

1 best response

Accepted Solutions
best response confirmed by valentingiaufer (Copper Contributor)
Solution

@valentingiaufer 

The Formula property expects a formula in English. You could use

 

ThisWorkbook.Sheets(SheetName).Range("H1").Formula = "=TEXTJOIN(""-"",,""Export"",SheetNameFunc(),YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

 

To specify it in your local language, use FormulaLocal:

 

ThisWorkbook.Sheets(SheetName).Range("H1").FormulaLocal = "=JOINDRE.TEXTE(""-"";;""Export"";SheetNameFunc();ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());JOUR(AUJOURDHUI()))

View solution in original post