How to get ctrl+shift+enter formulas to update in a macro?

[Call-of-Phoenix]

I am trying to rearrange data and have assembled an ugly macro that should;

Set year to cell B10.
UPDATE ALL FORMULAS AFTER THIS.
Then collect data from a range on sheet1 and print it to the first empty row on sheet2.
Set the next year and repeat.


Everything works except the "ctrl+shift+enter" -formulas won't update. Actually the {} disappear when opening the macro.
Is there an easy way to solve this without doing some hardcore coding? (just some command to update everything..?)

Here is the code (ugly and hardcoded, I know..) and picture of the file. {} should be in the formula of M12.

Also asked the same question at: https://www.mrexcel.com/forum/excel-...ate-macro.html

[Alf]

Can't see any formula defined for cell M12 but if cell formula was "=SUM(A1:E1)" and that should be entered in M12 as an array a code like this should work

Please Login or Register  to view this content.

Alf

[Call-of-Phoenix]

Thanks Alf for the quick response!

My idea is that I want to use the formula "{=IFERROR(INDEX(KaikkiFinat!$E$2:$U$71771;MATCH....)}" that's inputted already by hand in cell M12 (you see the formula in image2).

Actually I have these CSE(ctrl+shift+enter) -formulas in many other cells as well. These CSE formulas are using value of cell (10,2) which is the year that the data is from. I just wanted an easy way to update all the formulas after the macro has changed the year and then print out the results to another sheet. Works just fine with the regular formulas, but for some reason the CSE won't update..

If I understand you suggestion correctly you are right: I could try to work around the problem with your solution writing the cells again with the formulas been set again as FormulaArrays,
but think it will take a lot of time with my skills..

Anyways thank you for your answer.

[Call-of-Phoenix]

Sorry Ali for the Cross-posting. I am new to forums and didn't read the rules that carefully. I asked the moderator on the other side to remove the other thread.

[Call-of-Phoenix]

I am finding it difficult to modify my starting message on the mrexcel. I added the link to the end.

[AliGW]

Thanks.

In future, please post code directly in your post and put code tags (# on the toolbar) around them. Images cannot be copied easily!

[Alf]

As an image is a bit difficult to copy a complex formula from I'll use the short and not complete version in post #3

Please Login or Register  to view this content.

Not really sure of the " and where to add this, you will have to play around a bit with this probably in order to get the proper format.

Another alternative is to copy a cell that contains the array formula i.e. if G5 contained the array formula you needed you could copy this to M12 with this macro line

Please Login or Register  to view this content.

Alf

[Call-of-Phoenix]

Thanks Alf for trying to help me!

The easiest way to solve the problem for me was to just allow Excel to continue auto calculating by itself (using Vba macros stops excels autocalculating?).
I did this by adding

Please Login or Register  to view this content.

to the beginning of the code.