Fill cells with formula, one by one, AutoFill?

Hello,
can anyone tell me how I can fill out the cells of a column with a formula (please bear with me if I make any mistakes, English is not my native language. Thanks)

I have a table with thousands of data sets. The last 3 columns X Y Z are filled with formulas.

The problem is that every recalculation requires a few seconds. So, my idea was to write the formula only in the first row in the respective colums and fill every single cell by VBA one by one. The formula in the respective previous cell should be converted into value. I'm not sure but I think the less formula there are in the Sheet the faster it works.

After that the second, and finally the last column should be filled like this.

Is there any solucion for my problem?

I thought to do it with For...Next but I didn't manage it.

By the way, I am not really new in VBA and I find my way in the Editor and I am familiar with the syntax. But I can't write my own code yet.

Many thanks for your help


conf

Hello JapanDave,
thanks for your reply. It works excellently.

But unfortunately this is not what I was looking for. I wanted to caculate every cell individually because the Array Formula require some time to calculate each time when it starts calculating.


With your code it takes the same time as I would fill it manually.

Do you have any further idea?

Thank you for your help!

conf

Hello Roy,
I'm not sure but I think it would be faster if there is only one cell with a formula inside. The results in the colums of the respective row depend on each other.

I can't upload it, the data are quite sensitiv.

Thanks for your reply.

conf

Ah, I forgot to mention that sometimes the application hang during calculation. I think this happens because of the CPU-intensive formula.

Here you are, it's a simple copy of my sheet. Imagina how long it will work if you have thousands of rows and a plenty of columns to calculate.

Thanks Roy,
I have to go on looking for another way. Thanks all for your effort!

Have a nice day!

conf