Programmatically assign GetPivotData formula to a cell

1
votes

Run-time error '1004':

Application-defined or Object-defined error

I want set a cell formula with the code below:

Selection.Formula = "=GETPIVOTDATA(""Value"";$M$26;""M"";1;""Y"";1396)"

Above formula works in the sheet correctly as bellow:

=GETPIVOTDATA("Value";$M$26;"M";1;"Y";1396)

but when I try to assign to appropriate cell with macro, above error shown.

How can I assign this formula to cells, by VBA macro?

1
excelvbaexcel-formula

1 Answers

5
votes

VBA normal format is American English.

so either:

Change the ; to ,:

Selection.Formula = "=GETPIVOTDATA(""Value"",$M$26,""M"",1,""Y"",1396)"

Or use .FormulaLocal:

Selection.FormulaLocal = "=GETPIVOTDATA(""Value"";$M$26;""M"";1;""Y"";1396)"