Fill cells with formula, one by one, AutoFill?

**confusing** · 02-11-2012, 07:45 AM

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

**JapanDave** · 02-11-2012, 08:17 AM

Try this.

Sub d()

   Dim LR&
   
       LR = Cells(Rows.Count, 1).End(xlUp).Row
        Cells(1, 24).Resize(LR).Formula = "YourFormula"  'Your formula for x column
        Cells(1, 25).Resize(LR).Formula = "YourFormula"  'Your formula for y column
        Cells(1, 26).Resize(LR).Formula = "YourFormula"  'Your formula for z column
          
           Range("X1:Z" & LR).Value = Range("X1:Z" & LR).Value
           
           
           
End Sub

**royUK** · 02-11-2012, 08:24 AM

I can't see how using VBA would be quicker. Switch Calculation to manual, then re-calculate only when you need to

**confusing** · 02-11-2012, 08:33 AM

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

**confusing** · 02-11-2012, 08:39 AM

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

**confusing** · 02-11-2012, 08:42 AM

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

**JapanDave** · 02-11-2012, 08:44 AM

Post a workbook if you can?

**confusing** · 02-11-2012, 09:02 AM

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.

**royUK** · 02-11-2012, 09:13 AM

llThat number of array formulas will be slow. You can switch calculation to manual, then when necessary just calculate a specific cell or range or range of cells