VBA Formula Loop Question

[tejadajm]

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

 

[VoG]

Try

Sub Update_YPL()
With Range(Cells(6, 3), Cells(55, 233))
    .FormulaR1C1 = "=IF(R4C="""","""",SUMIF(INDIRECT(""'""&R1C&""'!$C$1:$C$2000""),RC1,INDIRECT(""'""&R1C&""'!""&R4C&"":""&R4C)))"
    .Value = .Value
End With
End Sub

 

[Jonmo1]

You can do it without a loop

Dim MyRange as Range
Set MyRange = Range(Cells(6,3),Cells(55,233))
MyRange.FormulaR1C1 = "=IF(R4C="""","""",SUMIF(INDIRECT(""'""&R1C&""'!$C$1:$C$2000""),RC1,INDIRECT(""'""&R1C&""'!""&R4C&"":""&R4C)))"
MyRange.Copy
MyRange.PasteSpecial xlPasteValues

Hope that helps.

 

[tejadajm]

Both of these solutions worked great. Thanks so much for the help and the quick response.