Forum Discussion
Automatically added @ in formulas
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()))
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()))
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 ?
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 !Credits go to PeterBartholomew1
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))),"")
Sorry, no idea. I cannot reproduce the problem.
