Hi,
I'm using excel 2013 (win7) to compile a file exported from SAP as excel 2007 (xlsx)
I converted a range in table (listobject)
I added some columns to the table (listobject) and filled those with some formulas
now I got some new hidden range names:
_xlfn.COUNTIFS
_xlfn.IFERROR
_xlfn.SUMIFS
(actually I'm using the new workbook functions COUNTIFS, IFERROR and SUMIFS)
all the ranges refers to #NAME? (some to #NOME? ... because I'm using the italian language)
I tried to rename, re-refer or delete the names with macros, no way!
I'could even leave the ranges, but I can't because it made me unable to use the table as source for a new Pivot table ... it gives me an error saying that it's impossible to get data from my table...I think that the error (#NAME) on the reference of the hidden names results in a reference error in my table.
The macro added the new formulas using the "formulaLocal" method, so I could write it in Italian, .
The macro finds the header name of each column in my table, then fill the formula and formats the cells.
I was triyng different combinations of formulas (english native or italian local) and different properties (Formula, FormulaLocal, FormulaR1C1, Formula R1C1Local) ...
I Tried the English sintax ("" + ,) with the property Formula or FormulaR1C1... still get the error
I Tried the European sintax (Italian Language + "" + ; ) with the property FormulaLocal or FormulaR1C1Local... still get the error
here is part of the code:
...
Any Suggestion?
Thanks
Dennis
I'm using excel 2013 (win7) to compile a file exported from SAP as excel 2007 (xlsx)
I converted a range in table (listobject)
I added some columns to the table (listobject) and filled those with some formulas
now I got some new hidden range names:
_xlfn.COUNTIFS
_xlfn.IFERROR
_xlfn.SUMIFS
(actually I'm using the new workbook functions COUNTIFS, IFERROR and SUMIFS)
all the ranges refers to #NAME? (some to #NOME? ... because I'm using the italian language)
I tried to rename, re-refer or delete the names with macros, no way!
I'could even leave the ranges, but I can't because it made me unable to use the table as source for a new Pivot table ... it gives me an error saying that it's impossible to get data from my table...I think that the error (#NAME) on the reference of the hidden names results in a reference error in my table.
The macro added the new formulas using the "formulaLocal" method, so I could write it in Italian, .
The macro finds the header name of each column in my table, then fill the formula and formats the cells.
I was triyng different combinations of formulas (english native or italian local) and different properties (Formula, FormulaLocal, FormulaR1C1, Formula R1C1Local) ...
I Tried the English sintax ("" + ,) with the property Formula or FormulaR1C1... still get the error
I Tried the European sintax (Italian Language + "" + ; ) with the property FormulaLocal or FormulaR1C1Local... still get the error
here is part of the code:
...
Code:
Application.Calculation = xlAutomatic '<<< ripristina il calcolo automatico
Application.ErrorCheckingOptions.BackgroundChecking = False
For Each c In objTable.ListColumns
'c.Select
MyT = c.Name
strColName = MyT
Select Case MyT
Case "destinee"
strFormula = "SE(O([@[Customer Document Code]]=""DOCUMENTO INTERNO"";SE.ERRORE(TROVA(""-"";[@[Doc Rev]])>0;FALSO));""Internal"";""customer"")"
strColFormat = "General"
Case "External"
strFormula = "SE([@[Customer Document Code]]=""DOCUMENTO INTERNO"";"""";SE.ERRORE(SE(TROVA(""-"";[@[Doc Rev]])>0;STRINGA.ESTRAI([@[Doc Rev]];1;TROVA("" - "";[@[Doc Rev]])-1));[@[Doc Rev]]))"
strColFormat = "0"
Case "Internal"
strFormula = "SE([@[Customer Document Code]]=""DOCUMENTO INTERNO"";SE([@[Doc Rev]]="""";CONTA.PIÙ.SE([Fores Doc Code];[@[Fores Doc Code]];[Customer Document Code];[@[Customer Document Code]];[Doc Rev];""<>""&"""";[destinee];""internal"")+1;SCARTO(TabREV['#];CONFRONTA([@[Doc Rev]];SE(CONTA.SE(TabREV[numeri];[@[Doc Rev]])>0;TabREV[numeri];TabREV[lettere]);0)-1;0;1;1));SE.ERRORE(SE(TROVA(""-"";[@[Doc Rev]])>0;SCARTO(TabREV['#];CONFRONTA(STRINGA.ESTRAI([@[Doc Rev]];TROVA(""-"";[@[Doc Rev]])+1;LUNGHEZZA([@[Doc Rev]])-TROVA(""-"";[@[Doc Rev]]));SE(CONTA.SE(TabREV[numeri];STRINGA.ESTRAI([@[Doc Rev]];TROVA(""-"";[@[Doc Rev]])+1;LUNGHEZZA([@[Doc Rev]])-TROVA(""-"";[@[Doc Rev]])))>0;TabREV[numeri];TabREV[lettere]);0)-1;0;1;1));""""))"
strColFormat = "0"
Case "Approval Within date"
strFormula = "SE(E([@[Issue Date]=]=>0;[@destinee]=""customer"");[@[Issue Date]]+ApprovalLeadTime;"""")"
strColFormat = "d/m/yyyy"
Case "Late Approval"
strFormula = "SE([@[Issue Date]=]=>0;SE(O([@[Approval Within date]=]=>OGGI();E([@[Approval Within date]=]=>[@[Response Date]];[@[Response Date]=]=>0));"""";SE([@[Response Date]=]=>0;[@[Response Date]];SE(CONTA.PIÙ.SE([Fores Doc Code];[@[Fores Doc Code]];[External];[@External]+1)>0;SE(SOMMA.PIÙ.SE([Issue Date];[Fores Doc Code];[@[Fores Doc Code]];[External];[@External]+1)>[@[Approval Within date]];SOMMA.PIÙ.SE([Issue Date];[Fores Doc Code];[@[Fores Doc Code]];[External];[@External]+1);[@[Approval Within date]]);OGGI()))-[@[Approval Within date]]);""n.a."")"
strColFormat = "0"
Case "Last Issue"
strFormula = "SE([@[Doc Rev]]="""";""Last"";SE(CONTA.PIÙ.SE([Fores Doc Code];[@[Fores Doc Code]];[Issue Date];"">""&[@[Issue Date]])>0;"""";SE(CONTA.PIÙ.SE([Fores Doc Code];[@[Fores Doc Code]];[Issue Date];"""")>0;"""";""Last"")))"
strColFormat = "General"
End Select
Select Case MyT
Case "destinee", "External", "Internal", "Approval Within date", "Late Approval", "Last Issue"
strFormula = "=" & strFormula
With objTable.ListColumns(strColName).DataBodyRange
.NumberFormat = strColFormat
' .Formula = strFormula
[B] .FormulaR1C1Local = strFormula[/B]
End With
End Select
Next c
Application.Calculation = xlManual '<<< disabilita il calcolo automatico (velocizza la macro)
Application.ErrorCheckingOptions.BackgroundChecking = TrueAny Suggestion?
Thanks
Dennis