#### ThrottleWorks

##### Excel Ninja

I am trying below mentioned formula in a worksheet.

Last row of the worksheet in 70,000 +.

I tried applying this formula at one go with usual method.

For example, Range(“A2:A” & TempLr) .FormulaR1C1 = "=IF(AND(COUNTIFS(C[1],RC[1],C[9],""YA*"")>0,COUNTIFS(C[1],RC[1],C[9],""SU*"")>0),""Bike"",""Car"")"

But my machine gets hanged with this operation.

That is why I am trying below loop. However this loop takes too much time to get complete.

Calculation mode is turned to manual before loops starts and turn to automatic once loop gets complete.

Can anyone please help me about how do I make this loop faster.

Code:

```
For Each TRng In TempRng
MySht.Cells(TRng.Row, 1).FormulaR1C1 = "=IF(AND(COUNTIFS(C[1],RC[1],C[9],""YA*"")>0,COUNTIFS(C[1],RC[1],C[9],""SU*"")>0),""Bike"",""Car"")"
Next TRng
```