Has anyone seen this and knows what's causing it and/or has a cure or workaround?
I have a workbook that pops up a user form that base on the selection will update the associated workbook sheets.
I have another workbook open at the same time that has several array formula in table that refer to other columns in that table using the structured format inside an IFERROR function - E.g. {=IFERROR(function of other columns us [field] and [@field]),"")}
What happens is when I make my selection on the first workbook's popup form (uses the DblClick method in a listbox) the underlying VBA calls a subroutine, Amongst other things, the subroutine updates the values in certain cells using the command format:
Workbooks("filename").Sheets("sheet name").Offset(r, c).FormulaR1C1 = string ~ value or actual formula string using the R1C1 format.
Everytime the VBA code hits one of these Workbooks... commands within the first workbook, the array formula in the second workbook all error causing the cells to blank in accordance with the IFERROR condition.
This is really wierd! It only seems to occur when I use FormulaR1C1 and not whenever I use other properties like Formula or NumberFormat with the same Workbooks().Sheets().Range().Offset(). prefix. Conveniently I also found that the FormulaR1C1 can be used to enter both literal text and formulas such as:
Workbooks().Sheets().Range().Offset().FormulaR1C1 = "Text" or
Workbooks().Sheets().Range().Offset().FormulaR1C1 = "INT(RC[-5])"
It happens everytime!
I've used the FormulaR1C1 property as I want to enter formulas with row and column relative addresses.
Is Excel broken? Am I doing something wrong? Is there a workaround?
Thx
I have a workbook that pops up a user form that base on the selection will update the associated workbook sheets.
I have another workbook open at the same time that has several array formula in table that refer to other columns in that table using the structured format inside an IFERROR function - E.g. {=IFERROR(function of other columns us [field] and [@field]),"")}
What happens is when I make my selection on the first workbook's popup form (uses the DblClick method in a listbox) the underlying VBA calls a subroutine, Amongst other things, the subroutine updates the values in certain cells using the command format:
Workbooks("filename").Sheets("sheet name").Offset(r, c).FormulaR1C1 = string ~ value or actual formula string using the R1C1 format.
Everytime the VBA code hits one of these Workbooks... commands within the first workbook, the array formula in the second workbook all error causing the cells to blank in accordance with the IFERROR condition.
This is really wierd! It only seems to occur when I use FormulaR1C1 and not whenever I use other properties like Formula or NumberFormat with the same Workbooks().Sheets().Range().Offset(). prefix. Conveniently I also found that the FormulaR1C1 can be used to enter both literal text and formulas such as:
Workbooks().Sheets().Range().Offset().FormulaR1C1 = "Text" or
Workbooks().Sheets().Range().Offset().FormulaR1C1 = "INT(RC[-5])"
It happens everytime!
I've used the FormulaR1C1 property as I want to enter formulas with row and column relative addresses.
Is Excel broken? Am I doing something wrong? Is there a workaround?
Thx