I have the following code that works well. However, because of the size of my file, this loop takes a long time to run. Is there anyway to streamline what I am doing and perhaps calculate the formula in the VBA script and only return the value to the cell in Excel? Any help is greatly appreciated.
Sub Update_YPL()
For rwNumber = 6 To 55
For clNumber = 3 To 233
Cells(rwNumber, clNumber).Select
ActiveCell.FormulaR1C1 = _
"=IF(R4C="""","""",SUMIF(INDIRECT(""'""&R1C&""'!$C$1:$C$2000""),RC1,INDIRECT(""'""&R1C&""'!""&R4C&"":""&R4C)))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next clNumber
Next rwNumber
End Sub
Sub Update_YPL()
For rwNumber = 6 To 55
For clNumber = 3 To 233
Cells(rwNumber, clNumber).Select
ActiveCell.FormulaR1C1 = _
"=IF(R4C="""","""",SUMIF(INDIRECT(""'""&R1C&""'!$C$1:$C$2000""),RC1,INDIRECT(""'""&R1C&""'!""&R4C&"":""&R4C)))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next clNumber
Next rwNumber
End Sub