title: Macro to Auto Fill Down VLookUp Results down to last adjacent cell
body: <p>I am trying to make a macro that will run a <code>vlookup</code> and then autofill down to the last adjacent cell that contains data. When I originally created the macro the dataset contained 1917 lines of data which is why you will continue to see it reference <code>1917</code>. However, the dataset varies in size daily.</p>
  
  <p>This particular macro takes a couple of steps before getting to this point:</p>
  
  <ol>
  <li>Converts text to columns</li>
  <li>Deletes an unnecessary column that is provided from a supporting report</li>
  <li>Adds titles to 5 columns</li>
  <li>Resizes all of the columns to fix the data</li>
  <li>Then it runs the first <code>vlookup</code> which I need the data to fill down to the last adjacent cell to the left</li>
  <li>Then it runs another <code>vlookup</code> which I need to also fill down to the last adjacent cell to the left</li>
  </ol>
  
  <p>Here is the code for the two <code>vlookup</code> I am struggling with:</p>
  
  <pre><code>Range("E2").Select
  ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-4],'CURRENT DAY'!R[-1]C[-4]:R[2498]C[1],5,0)"
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'CURRENT DAY'!R1C1:R2500C6,5,0)"
  Range("E2").Select
  Selection.AutoFill Destination:=Range("E2:E1917")
  Range("E2:E1917").Select
  Columns("E:E").EntireColumn.AutoFit
  Range("F2").Select
  ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-5],'CURRENT DAY'!R[-1]C[-5]:R[2498]C,6,0)"
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],'CURRENT DAY'!R1C1:R2500C6,6,0)"
  Range("F2").Select
  Selection.AutoFill Destination:=Range("F2:F1917")
  Range("F2:F1917").Select
  </code></pre>