_xlfn... hidden range names results in data retrieve error when pivoting

[Den75]

Hi, I'm new in this Forum, but I'm a follower/reader. Usually it's enough because I found the answers to all my questions, unluckily not today-

help!!
I'm using excel 2013 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 and 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.

here is part of the 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"
​

​

' FormulaR1C1 =IF(OR([@[Customer Document Code]]="DOCUMENTO INTERNO",IFERROR(FIND("-",[@[Doc Rev]])>0,FALSE)),"Internal","customer")
' FormulaR1C1Local =SE(O([@[Customer Document Code]]="DOCUMENTO INTERNO";SE.ERRORE(TROVA("-";[@[Doc Rev]])>0;FALSO));"Internal";"customer")

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]]))"
​

​

' FormulaR1C1 =IF([@[Customer Document Code]]="DOCUMENTO INTERNO","",IFERROR(IF(FIND("-",[@[Doc Rev]])>0,MID([@[Doc Rev]],1,FIND(" - ",[@[Doc Rev]])-1)),[@[Doc Rev]]))
' FormulaR1C1Local =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));""""))"
​

​

' FormulaR1C1 "=IF([@[Customer Document Code]]=""DOCUMENTO INTERNO"",IF([@[Doc Rev]]="""",COUNTIFS([Fores Doc Code],[@[Fores Doc Code]],[Customer Document Code],[@[Customer Document Code]],[Doc Rev],""<>""&"""",[destinee],""internal"")+1,OFFSET(TabREV['#],MATCH([@[Doc Rev]],IF(COUNTIF(TabREV[numeri],[@[Doc Rev]])>0,TabREV[numeri],TabREV[lettere]),0)-1,0,1,1)),IFERROR(IF(FIND(""-"",[@[Doc Rev]])>0,OFFSET(TabREV['#],MATCH(MID([@[Doc Rev]],FIND(""-"",[@[Doc Rev]])+1,LEN([@[Doc Rev]])-FIND(""-"",[@[Doc Rev]])),IF(COUNTIF(TabREV[numeri],MID([@[Doc Rev]],FIND(""-"",[@[Doc Rev]])+1,LEN([@[Doc Rev]])-FIND(""-"",[@[Doc Rev]])))>0,TabREV[numeri],TabREV[lettere]),0)-1,0,1,1)),""""))"
' FormulaR1C1Local "=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;"""")"
​

​

' Formula "=IF(AND([@[Issue Date]=]=>0,[@destinee]=""customer""),[@[Issue Date]]+ApprovalLeadTime,"""")"
' FormulaLocal "=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."")"
​

' FormulaR1C1 "=IF([@[Issue Date]=]=>0,IF(OR([@[Approval Within date]=]=>TODAY(),AND([@[Approval Within date]=]=>[@[Response Date]],[@[Response Date]=]=>0)),"""",IF([@[Response Date]=]=>0,[@[Response Date]],IF(COUNTIFS([Fores Doc Code],[@[Fores Doc Code]],[External],[@External]+1)>0,IF(SUMIFS([Issue Date],[Fores Doc Code],[@[Fores Doc Code]],[External],[@External]+1)>[@[Approval Within date]],SUMIFS([Issue Date],[Fores Doc Code],[@[Fores Doc Code]],[External],[@External]+1),[@[Approval Within date]]),TODAY()))-[@[Approval Within date]]),""n.a."")"

' FormulaR1C1Local "=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"")))"
​

​

' FormulaR1C1 =IF([@[Doc Rev]]="""",""Last"",IF(COUNTIFS([Fores Doc Code],[@[Fores Doc Code]],[Issue Date],"">""&[@[Issue Date]])>0,"""",IF(COUNTIFS([Fores Doc Code],[@[Fores Doc Code]],[Issue Date],"""")>0,"""",""Last"")))
' FormulaR1C1Local =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
.FormulaR1C1 = strFormula
' .FormulaR1C1Local = strFormula
​

End With
​

End Select
​

Next c

Application.Calculation = xlManual '<<< disabilita il calcolo automatico (velocizza la macro)

Application.ErrorCheckingOptions.BackgroundChecking = True

...

Thanks for your help

Dennis

(win 7, Office 2013 Professional, Excel 2013, VBA)

 

[RoryA]

You don't appear to actually be using the FormulaR1C1Local property there - you're using a localised formula, but passing it to the FormulaR1C1 property, which won't work.

 

[Den75]

Thanks RoryA,
actually you're right, I was triyng different combinations of formulas (english native or italian local) and different properties (Formula, FormulaLocal, FormulaR1C1, Formula R1C1Local) and i pasted the wrong code...

it should have been:

With objTable.ListColumns(strColName).DataBodyRange[INDENT].NumberFormat = strColFormat
[/INDENT]
[INDENT]'.[B]FormulaR1C1 = strFormula[/B]
.FormulaR1C1[B]Local[/B] = strFormula
[/INDENT]
 End With

Sorry

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

now I've tried this (

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"
                [INDENT=2]strFormula = "=" & strFormula
[/INDENT]
                 
                With objTable.ListColumns(strColName).DataBodyRange
[INDENT=3].NumberFormat = strColFormat
[/INDENT]
 [INDENT=3]'.Formula = strFormula
[/INDENT]
[INDENT=3][B].FormulaR1C1Local = strFormula[/B]
[/INDENT]
                 End With
                
        End Select
    Next c
    
    Application.Calculation = xlManual     '<<< disabilita il calcolo automatico (velocizza la macro)
    
    Application.ErrorCheckingOptions.BackgroundChecking = True

ANY SUGGESTION?

Thanks again

Dennis

PS: I GUESS the problem is in the hidden ranges, but I'm not sure... the issue is that the data contained in the Table are unuseful for pivoting