VBA FormulaR1C1 function errors array formula in cells of another workbook

Mrock1

Board Regular
Joined
Oct 7, 2014
Messages
78
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top