Writing an array formula to a large number of cells

I am working on a script to write an array formula to a large number of cells. I have a code that works, but is very slow. Any thoughts on how to speed it up? The end goal is to be able to quickly write an array formula to several cells. The problem is that each cell has a unique formula. So I couldn't think of a way to simplify the code. Here is the code as it is written now:

```
Sub make_sheet()

Dim m As Integer
Dim h As Integer
Dim a As Integer
Dim b As Integer
Dim dt As String
Dim man As String

m = 10005
h = 2
a = 1
dt = ActiveWorkbook.Sheets("DCAM2_REVD-ODB-11-18-2016_2017-").Cells(2, 4).Value
man = InputBox("Who Manufactured the PCB?", "Manufacture")

ActiveWorkbook.Sheets("Sheet1").Cells(1, 6).FormulaR1C1 = "time/date stamp of test"
ActiveWorkbook.Sheets("Sheet1").Cells(1, 7).FormulaR1C1 = "Manufacturer"
ActiveWorkbook.Sheets("Sheet1").Cells(1, 12).FormulaR1C1 = "Channel"

For i = 2 To 17 Step 1
ActiveWorkbook.Sheets("Sheet1").Cells(i, 6).FormulaR1C1 = dt
Next i

For i = 2 To 17 Step 1
ActiveWorkbook.Sheets("Sheet1").Cells(i, 7).FormulaR1C1 = man
Next i

b = 0
For i = 2 To 11 Step 1
ActiveWorkbook.Sheets("Sheet1").Cells(i, 12).FormulaR1C1 = "DS0" & b & ""
b = b + 1
Next i

b = 10
For i = 12 To 17 Step 1
ActiveWorkbook.Sheets("Sheet1").Cells(i, 12).FormulaR1C1 = "DS" & b & ""
b = b + 1
Next i

For i = 13 To 17 Step 1
ActiveWorkbook.Sheets("Sheet1").Cells(1, i).FormulaR1C1 = "A" & a & ""
a = a + 1
Next i

a = 1
For i = 18 To 29 Step 1
ActiveWorkbook.Sheets("Sheet1").Cells(1, i).FormulaR1C1 = "P" & a & ""
a = a + 1
Next i

For i = 13 To 17 Step 1
ActiveWorkbook.Sheets("Sheet1").Cells(2, i).FormulaArray = "=Product(0.03937,(VLOOKUP(""U" & m & """&""#_" & h & "_13_Hei"",CHOOSE({1,2},'DCAM2_REVD-ODB-11-18-2016_2017-'!B:B&'DCAM2_REVD-ODB-11-18-2016_2017-'!E:E,'DCAM2_REVD-ODB-11-18-2016_2017-'!H:H),2,0)))"
ActiveWorkbook.Sheets("Sheet1").Cells(2, i).NumberFormat = "0.00"
h = h + 1
Next i

h = 1
For i = 18 To 29 Step 1
ActiveWorkbook.Sheets("Sheet1").Cells(2, i).Fo

Every time you plug a formula into an Excel cell it causes the application to recalculate the new cell and any other cells that were affected indirectly. When you start updating hundreds of cells via macro, it starts to bog things down. This is especially true when formulas are intensive (ie, array formulas).

To circumvent this issue, you can temporarily turn off automatic calculation in Excel using the Application's Calculation property. Doing this will cause Excel to only recalculate once, after you've finished entering all the new formulas.

Try wrapping your code with the following:

Application.Calculation = xlCalculationManual

'CODE CODE CODE

Application.Calculation = xlCalculationAutomatic
Application.Calculate

StackExchange Code Review Q#158657, answer score: 2