I write decently sloppy code and piecemeal together various things I find online to help me accomplish what needs to be done. This loop goes through 4 different worksheets to paste a formula in 3 columns. Based on some previous feedback from the site, I removed all of my .Select
references and hopefully did things more cleanly. 2 questions that I have:
1) Can anyone think of a way I can further optimize this to have it run more quickly?
2) Is there a better way to define my RRSData!A2:O10000 formula. I just chose 10,000 because there shouldn't be that many in the future, but its obviously working harder than it needs to now since there are only ~1000 rows currently.
Dim LastRow1 As Long, i1 As Long, InxW As Long
Dim wsNames As Variant
wsNames = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
For InxW = LBound(wsNames) To UBound(wsNames)
With Worksheets(wsNames(InxW))
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i1 = 25 To LastRow
With Range("KA1").Offset(i1 - 1, 0)
.Formula = "=IF(SUMIF(RRSData!$A$2:$O$10000,OFFSET($KA$1,CELL(""row"",THIS),-285),RRSData!$M$2:$M$10000)>0,SUMIF(RRSData!$A$2:$O$10000,OFFSET($KA$1,CELL(""row"",THIS),-285),RRSData!$M$2:$M$10000),"""")"
.NumberFormat = "0.00"
.Value = .Value
End With
With Range("KB1").Offset(i1 - 1, 0)
.FormulaR1C1 = "=IF(RC[-1]="""","""",If(RC[-1]>1.1,""High"",If(RC[-1]<0.8,""Low"",""Neutral"")))"
.Value = .Value
End With
With Range("KC1").Offset(i1 - 1, 0)
.FormulaR1C1 = "=IF(RC[-2]="""","""",IF(RC[-2]>1.1,""+5%"",IF(RC[-2]<0.8,""-5%"",""0%"")))"
.Value = .Value
End With
Next i1
End With
Next InxW
The code works, but it takes probably 2 minutes to loop through the four sheets, and the total number of rows across the four sheets is <500
Ask Question
button. With more information we can give you a better and more thorough review. \$\endgroup\$