Inserting a formula into a cell

[kuhni]

Hi everybody,

I have a little problem concerning Formulas in Excel VBA.

I want to insert the following code into an cell using VBA:
=GETPIVOTDATA("Cost ";'Reports.xls'pivotYear;"Year";2003)

Of course, I want to create this statement generic, e.g.:
Range("G5").FormulaR1C1 = "=GETPIVOTDATA(""" & pos &
""";'Reports.xls'!" & pivot & ";""Year"";2003)"

I have tested the syntax of the string and it is identically to the
string above. The problem is now that I receive an error message when
it comes to this statement: Range("G5").FormulaR1C! = "..."
Error message: Application-defined or Object-defined error

I would be very grateful, if somebody could help me how to do it
properly! THANKS!


Best wishes,
Stephan

 

[Tom Ogilvy]

FormulaR1C1 is looking for US syntax, so the semicolons are causing the
problem. Either replace the semicolons with commas or use

FormulaR1C1Local

if you use Local, then the formula would have to be exactly as you would
enter it by hand. (re: GetPivotData; would you use that function name; as
an example).

 

[kuhni]

It works! Thanks a lot!

Nevertheless, I think this is strange: although I have to use the
English/International command like "GETPIVOTDATA" there are
differences in local and international syntax. For me, it doesn't make
sense. They should avoid these differences when using commands because
it's only confusing.

Best wishes,
Stephan