title: Excel VBA Range variables and Autofill
body: <p>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.</p>
  
  <pre><code>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]&gt;0)*AND(RC[-20]&gt;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]&gt;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]&lt;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]&gt;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]&gt;0)*AND(RC[-24]&gt;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]&lt;&gt;0)*AND(RC[-17]&gt;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)
  </code></pre>