VBA Excel calculating slowly -
i have macro taking long time process. main reason loop populating range formula.
to speed things used autofill function on range in order populate formula faster. works, when run macro need press shift+f9 in order update formula.
for excel process calculations taking 20 minutes, while takes 20 seconds populate range formula.
is there workaround make excel update formulas faster?
i have tried breaking down range , using activesheet.calculation function slows down.
sheets("sheet").range(cl1 & i.row).formula = "=round(if(trim(left(" & cl1 & "$2,4))="""",""missing value"",if($b" & i.row & "=""sheet1"",sumifs(indirect(""'""&$b" & i.row & "&""'!""&substitute(address(1,match(trim(left(" & cl1 & "$2,4))&"" - total"",sheet2!$1:$1,0),4),""1"","""")&"":""&" & _ "substitute(address(1,match(trim(left(" & cl1 & "$2,4))&"" - total"",sheet2!$1:$1,0),4),""1"","""")),indirect(""'""&$b" & i.row & "&""'!""&substitute(address(1,match($c$3,sheet2!$1:$1,0),4),""1"","""")&"":""& " & _ "substitute(address(1,match($c$3,sheet2!$1:$1,0),4),""1"","""")),$c" & i.row & ",indirect(""'""&$b" & i.row & "&""'!""&substitute(address(1,match($d$3,sheet2!$1:$1,0),4),""1"","""")&"":""&" & _ "substitute(address(1,match($d$3,sheet2!$1:$1,0),4),""1"","""")),$d" & i.row & "),if($b" & i.row & "=""soi"",iferror(sumifs(indirect(""'soi'!""&substitute(address(1,match($c" & i.row & ", sheet3!$1:$1,0)+2,4),""1""," & """"") & "":"" &substitute(address(1,match($c" & i.row & ",soi!$1:$1,0)+2,4),""1"","""")),indirect(""'soi'!""&substitute(address(1,match($c" & i.row & ",soi!$1:$1,0)+0,4),""1"","""") & "":"" &substitute(address(1," & _ "match($c" & i.row & ",sheet4!$1:$1,0)+0,4),""1"","""")),trim(left(" & cl1 & "$2,4)),indirect(""'soi'!""&substitute(address(1,match($c" & i.row & ", sheet3!$1:$1,0)+1,4),""1"","""") & "":"" &substitute(address(1,match($c" & i.row & ",soi!$1:$1,0)+1," & "4),""1"","""")),$d" & i.row & "),""classification error""),if(b" & i.row & "=""unrealised"",index(indirect($b" & i.row & "&""!a:zz""),match($d" & i.row & ",indirect($b" & i.row & "&""!a:a""),0),match(trim(left(" & cl1 & "$2,4))&"" - movement"",indirect($b" & i.row & "&""!2:2""),0)),""sheet name error"")" & _ ",if(b" & i.row & "=""sofp"",sumif(sheet4!" & str & ": a" & fin_row & ", a" & i.row & ",index(sheet3!e" & str_row & ":" & fin_col & fin_row & ",0,match(left(" & cl1 & "$2,4), sheet3!" & "e2:" & fin_col & "2,0))),""sheet name error""))),)"
if can disable update of screen , calculations during loop, considerably improve performance, if each formula insert forces rest recalculate.
Comments
Post a Comment