Excel VBA Range variables and Autofill

Question

I'm adding columns to the end of the table. First headings and then formulas in the column underneath. Then I want to Autofill the formulas for the whole list. I believe the variable declaration is the problem. I keep on getting an error at the Autofill section at the bottom.

Dim LastCol As Long, lastrow As Long
Dim OOSCol As Long, LCell As Long
Dim LRange As Range
Dim ws As Worksheet
Set ws = Application.ActiveWorkbook.ActiveSheet    

With ws
    .Name = "Summary"
    LastCol = .Cells(51, .Columns.Count).End(xlToLeft).Column
    .Cells(51, LastCol + 1).Value = "OOS NO SOO"
    .Cells(52, LastCol + 1).FormulaR1C1 = "=IF((RC[-21]=4)*AND(RC[-20]=0)*AND(RC[-19]=0)*AND(RC[-3]=""Roster"")*AND(RC[-4]=""Listed""),1,"""")"
    .Cells(51, LastCol + 2).Value = "OOS SOO"
    .Cells(52, LastCol + 2).FormulaR1C1 = "=IF((RC[-22]=4)*AND(RC[-21]>0)*AND(RC[-20]>0)*AND(RC[-4]=""Roster"")*AND(RC[-5]=""Listed""),1,"""")"
    .Cells(51, LastCol + 3).Value = "SOH NO SOO"
    .Cells(52, LastCol + 3).FormulaR1C1 = "=IF((RC[-23]=4)*AND(RC[-22]>0)*AND(RC[-21]=0)*AND(RC[-5]=""Roster"")*AND(RC[-6]=""Listed""),1,"""")"
    .Cells(51, LastCol + 4).Value = "Negative Stock"
    .Cells(52, LastCol + 4).FormulaR1C1 = "=IF((RC[-24]=4)*AND(RC[-23]<0)*AND(RC[-6]=""Roster"")*AND(RC[-7]=""Listed""),1,"""")"
    .Cells(51, LastCol + 5).Value = "Overstock"
    .Cells(52, LastCol + 5).FormulaR1C1 = "=IF((RC[-25]=4)*AND(RC[-21]>14)*AND(RC[-7]=""Roster"")*AND(RC[-8]=""Listed""),1,"""")"
    .Cells(51, LastCol + 6).Value = "Dormant Stock"
    .Cells(52, LastCol + 6).FormulaR1C1 = "=IF((RC[-26]=4)*AND(RC[-25]>0)*AND(RC[-24]>10)*AND(RC[-8]=""Roster"")*AND(RC[-9]=""Listed""),1,"""")"
    .Cells(51, LastCol + 7).Value = "Outdated Stock Counts"
    .Cells(52, LastCol + 7).FormulaR1C1 = "=IF((RC[-27]=4)*AND(RC[-26]<>0)*AND(RC[-17]>30)*AND(RC[-9]=""Roster"")*AND(RC[-10]=""Listed""),1,"""")"
    .Cells(51, LastCol + 8).Value = "Total Issues"
    .Cells(52, LastCol + 8).FormulaR1C1 = "=SUM(RC[-7]:RC[-1])*AND(RC[-10]=""Roster"")*AND(RC[-11]=""Listed"")"
    .Range("A51", Cells(51, Columns.Count).End(xlToRight)).SpecialCells(xlCellTypeConstants).Interior.Color = RGB(0, 32, 96)
    .Range("A51", Cells(51, Columns.Count).End(xlToRight)).SpecialCells(xlCellTypeConstants).Font.Color = vbWhite

End With

With ws
    LastCol = .Cells(51, .Columns.Count).End(xlToLeft).Column
    OOSCol = .Cells(52, LastCol - 8)
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LCell = .Cells(lastrow, LastCol)
End With

LRange = Range(OOSCol, LCell).Select
Selection.AutoFill Destination:=Range(OOSCol, LCell)

Answer 1

I managed to fix it with the following code with help from a different site. See the last line of code. That's what worked.

Dim LastCol As Long, lastrow As Long
Dim ws As Worksheet
Set ws = Application.ActiveWorkbook.ActiveSheet

With ws
    .Name = "Summary"
    LastCol = .Cells(51, .Columns.Count).End(xlToLeft).Column
    .Cells(51, LastCol + 1).Value = "OOS NO SOO"
    .Cells(52, LastCol + 1).FormulaR1C1 = "=IF((RC[-21]=4)*AND(RC[-20]=0)*AND(RC[-19]=0)*AND(RC[-3]=""Roster"")*AND(RC[-4]=""Listed""),1,"""")"
    .Cells(51, LastCol + 2).Value = "OOS SOO"
    .Cells(52, LastCol + 2).FormulaR1C1 = "=IF((RC[-22]=4)*AND(RC[-21]>0)*AND(RC[-20]>0)*AND(RC[-4]=""Roster"")*AND(RC[-5]=""Listed""),1,"""")"
    .Cells(51, LastCol + 3).Value = "SOH NO SOO"
    .Cells(52, LastCol + 3).FormulaR1C1 = "=IF((RC[-23]=4)*AND(RC[-22]>0)*AND(RC[-21]=0)*AND(RC[-5]=""Roster"")*AND(RC[-6]=""Listed""),1,"""")"
    .Cells(51, LastCol + 4).Value = "Negative Stock"
    .Cells(52, LastCol + 4).FormulaR1C1 = "=IF((RC[-24]=4)*AND(RC[-23]<0)*AND(RC[-6]=""Roster"")*AND(RC[-7]=""Listed""),1,"""")"
    .Cells(51, LastCol + 5).Value = "Overstock"
    .Cells(52, LastCol + 5).FormulaR1C1 = "=IF((RC[-25]=4)*AND(RC[-21]>14)*AND(RC[-7]=""Roster"")*AND(RC[-8]=""Listed""),1,"""")"
    .Cells(51, LastCol + 6).Value = "Dormant Stock"
    .Cells(52, LastCol + 6).FormulaR1C1 = "=IF((RC[-26]=4)*AND(RC[-25]>0)*AND(RC[-24]>10)*AND(RC[-8]=""Roster"")*AND(RC[-9]=""Listed""),1,"""")"
    .Cells(51, LastCol + 7).Value = "Outdated Stock Counts"
    .Cells(52, LastCol + 7).FormulaR1C1 = "=IF((RC[-27]=4)*AND(RC[-26]<>0)*AND(RC[-17]>30)*AND(RC[-9]=""Roster"")*AND(RC[-10]=""Listed""),1,"""")"
    .Cells(51, LastCol + 8).Value = "Total Issues"
    .Cells(52, LastCol + 8).FormulaR1C1 = "=SUM(RC[-7]:RC[-1])*AND(RC[-10]=""Roster"")*AND(RC[-11]=""Listed"")"
    .Range("A51", Cells(51, Columns.Count).End(xlToRight)).SpecialCells(xlCellTypeConstants).Interior.Color = RGB(0, 32, 96)
    .Range("A51", Cells(51, Columns.Count).End(xlToRight)).SpecialCells(xlCellTypeConstants).Font.Color = vbWhite

End With

With ws
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
End With

Cells(52, LastCol + 1).Resize(1, 8).AutoFill Destination:=Range(Cells(52, LastCol + 1), Cells(lastrow, LastCol + 8))