Hello. I have encountered a situation of incorrect work of

Api.RecalculateAllFormulas();

I want to change the values in the cells that the formula uses in a loop, immediately recalculate and insert at each iteration the updated result in the desired order.

At first the function worked with a shift by one iteration, i.e. instead of N the calculation for N -1 was inserted (the initial value before all changes is inserted at the first iteration). Then it started calculating correctly after the second line in a row

Api.RecalculateAllFormulas();

After restarting the application, at some point it started counting incorrectly. If you use it 1 time, it counts with a shift, but the results with incorrect numbers. If you use it 2 times, it counts without shift, but the results are incorrect too. And I can’t get it to work correctly.

It looks like an intermediate calculation gets into the formula, i.e. the recalculation has not been completed, and an intermediate value is taken from the cell with the formula.

Without correct work of

Api.RecalculateAllFormulas();

it is not clear what to do next.

In the macro I tried to implement an analog of Excel data table.

There is no possibility to attach an example here.