VBA Loop through multiple sheets to output formulas in multiple columns

Question

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

Answer 1

A couple of tips.

When using With, make sure that you correctly reference the subordinate lines. Check the following example.

With Range("KA1").Offset(i1 - 1, 0) '<--- You have ...
With .Range("KA1").Offset(i1 - 1, 0) ' ... should be

Consider using FillDown or some similar command to do bulk edits. This will save looping, especially the loop that flips between the VBA engine and the Excel engine (which is expensive time-wise). Normally I suggest using arrays, but your use of formula suggests this other way.

The following example has not been tested.

Dim formulaOneRange as Range
    Set formulaOneRange = .Range("KA25:KA" & lastRow)
    With formulaOneRange
        .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
    Set formulaOneRange = .Range("KB25:KB" & lastRow)
    With formulaOneRange
        .FormulaR1C1 = "=IF(RC[-1]="""","""",If(RC[-1]>1.1,""High"",If(RC[-1]<0.8,""Low"",""Neutral"")))"
        .Value = .Value
    End With
    Set formulaOneRange = .Range("KC25:KC" & lastRow)
    With formulaOneRange
        .FormulaR1C1 = "=IF(RC[-1]="""","""",If(RC[-1]>1.1,""High"",If(RC[-1]<0.8,""Low"",""Neutral"")))"
        .Value = .Value
    End With

Of course, the code could be tidies up a bit as per below, but I thought the extra lines will show my thinking a bit more clearly.

' My example code above ... 
Set formulaOneRange = .Range("KC25:KC" & lastRow)
With formulaOneRange

' Could be ....
With .Range("KC25:KC" & lastRow)

Also, defining the range in an easy to manage variable would assist if you wanted to use FillDown or do some other manipulation within each section.