Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a formula into a cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a formula into a cell
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. ( GetPivotData; would you use that function name; as an example). -- Regards, Tom Ogilvy "kuhni" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a formula into a cell
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting formula in the cell below | Excel Worksheet Functions | |||
Inserting formula in the cell below | Excel Worksheet Functions | |||
Problem After Inserting Cell Formula | Excel Discussion (Misc queries) | |||
help with inserting a cell value into a named formula | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel |