A faster way to process to multiple sheets/cells

[CookieMonster76]

Hi

I have the following (extract for illustration):
For Each ws In Sheets(Array("Sheet2", "Sheet2")

With ws.Range("C12")
.FormulaR1C1 = "=-ROUND(SUMIFS('TB actual'!C4,'TB actual'!C3,RC[4],'TB actual'!C[-2],R3C2)
+ SUMIFS('TB actual'!C4,'TB actual'!C3,RC[5],'TB actual'!C[-2],R3C2)
+ SUMIFS('TB a ctual'!C4,'TB actual'!C3,RC[6],'TB actual'!C[-2],R3C2)
+ SUMIFS('TB actual'!C4,'TB actual'!C3,RC[7],'TB actual'!C[-2],R3C2)
+SUMIFS('TB actual'!C4,'TB actual'!C3,RC[8],'TB actual'!C[-2],R3C2)
+SUMIFS('TB actual'!C4,'TB actual'!C3,RC[9],'TB actual'!C[-2],R3C2),0)"
.Value2 = .Value2
End With

With ws.Range("C13")
.FormulaR1C1 = "=-ROUND(SUMIFS('TB actual'!C4,'TB actual'!C3,RC[4],'TB actual'!C[-2],R3C2)
+ SUMIFS('TB actual'!C4,'TB actual'!C3,RC[5],'TB actual'!C[-2],R3C2)
+ SUMIFS('TB a ctual'!C4,'TB actual'!C3,RC[6],'TB actual'!C[-2],R3C2)
+ SUMIFS('TB actual'!C4,'TB actual'!C3,RC[7],'TB actual'!C[-2],R3C2)
+SUMIFS('TB actual'!C4,'TB actual'!C3,RC[8],'TB actual'!C[-2],R3C2)
+SUMIFS('TB actual'!C4,'TB actual'!C3,RC[9],'TB actual'!C[-2],R3C2),0)"
.Value2 = .Value2
End With

Next ws


It posts to 157 cells on over 200 sheets, but with exactly the same code in each one. It is currently taking 8 minutes to run.

I have screen updating and calculation turned off.

Is it simply the volume of transaction or is there a way to speed it up?

Thanks

Paul

 

[Fazza]

Hi. Paul

I'll assume there is no event code being triggered.
It looks like an inefficient formula. If so, try another approach. Can only guess as have no information - with 200 worksheets maybe an alternative set up is possible - more like a database.

regards