Apologies if this is the wrong forum...
I've tested this in both Office 2010 and 2013. In Excel, if I run a macro that assigns a formula to a cell using the .FormulaR1C1 property while a Chart sheet is active (that is, you create a chart, right click, Move Chart ..., New sheet), the formula that gets written to a cell reassigns the relative references as if they were relative to cell A1.
So, for example, the following code:
sub test() ThisWorkbook.Sheets("Sheet1").Cells(2, 3).FormulaR1C1 = "=R[4]C[5]" end sub
If I run that with a normal sheet active, the formula that gets written to cell C2 is "=H6", exactly as expected - four rows down from row 2, and 5 columns right from column C. If I run that same code with a Chart sheet active, the formula
that gets written to cell C2 is "=F5". That's 4 rows down and 5 columns right from A1. Similarly,
sub test() ThisWorkbook.Sheets("Sheet1").Cells(2, 3).FormulaR1C1 = "=R[-1]C[-1]" end sub
will write "=XFD1048576" to cell C2, which is the last row and last column of the worksheet (that is, one row above and one column left of A1, looping around to the end of the sheet).
An easy workaround is to simply activate a regular sheet prior to writing any R1C1-style formulas. But I'm hoping this bug can be fixed. Searching around on the web doesn't really yield much information about this, which I'm sort of surprised by. I would think that more people would have run into it. But I'm only able to find this reference
http://www.mrexcel.com/forum/excel-questions/328850-formular1c1-doesnt-work-when-chart-active-why.html
and this snippet of code (which doesn't seem to actually get discussed anywhere)
https://gist.github.com/miya2000/10786520
Is this a known issue? Is there a fix in work?
- Edited by tomsing98 11 hours 20 minutes ago