Hi,

I am aware of that the SUBTOTAL formula works fine, but if I want to insert the norwegian translated SUBTOTAL formula ("DELSUM") below the column named "TotalCost" in a defined table (list) something like this:

ActiveSheet.Range("c6:c8").Select
ActiveWorkbook.Names.Add Name:="rangeArea", RefersTo:=Selection
Range("c9").Select
Selection.FormulaR1C1 = "=DELSUM(9,rangeArea)"

Excel does not recognize the "DELSUM" formula name unless I actually click the Formula line afterwards. When I select "Explore formula" from the Excel Tools menu, it confirms that the formula name is unknown.

If I record a macro when I click the formula line, it reveals the two lines generated by Excel:

Range("autoTabel1[[#Totals],[TotalCost]]").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,sumArea)"

I could not find this requirement documented anywhere - is it a bug in Excel?



Regards

Frank

Hi Frank,

Am Tue, 7 Jul 2015 02:04:27 -0700 (PDT) schrieb :

ActiveSheet.Range("c6:c8").Select
ActiveWorkbook.Names.Add Name:="rangeArea", RefersTo:=Selection
Range("c9").Select
Selection.FormulaR1C1 = "=DELSUM(9,rangeArea)"

Excel does not recognize the "DELSUM" formula name unless I actually click the Formula line afterwards. When I select "Explore formula" from the Excel Tools menu, it confirms that the formula name is unknown.

Use in VBA the english function names:
Range("C9").Formula = "=SUBTOTAL(9,rangeArea)"
This formula will be translated to your system langunage into the sheet.

If you want to use norwegian function names try it with FormulaLocal:
Range("C9").FormulaLocal = "=DELSUM(9,rangeArea)"

Check the separator into the line above. I don't know the norwegian
separators. If you use FormulaLocal you also have to use them.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional