title: Excel Vlookup with Autofill VBA
body: <p>I'm trying to make a macro that seems pretty simple, but it's proven to be harder than I thought. I started making it using the "point and click" method, but it hasn't been as dynamic as I had thought. Basically I'm using vlookup to look up the names in cell A2 from another worksheet, then copy numbers in column D over to the current sheet. When looking at the code, it looks something like this for this part:</p>
  
  <pre><code>Range("D2").Select
  ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-3],'Downstairs'!R[-1]C[-3]:R[200]C[14],4,0)"
  
  Range("D2").Select
  Selection.AutoFill Destination:=Range("D2:D" &amp; lastrow&amp;)
  </code></pre>
  
  <p>After I find the first number, I'm trying to autofill the rest. It works when you run it but I think it's just because the names are in order. However, if the names aren't in order it won't find the correct number, partly because all the numbers inside the R[] and C[] keep changing as you go down the rows. I don't think it will work if I add more names to the list, which is something I need for it to do.  I changed the number inside the R[] from 93 to 200 because I don't really know how to incorporate the lastrow object I had made before, and I don't know of another way to make this dynamic. Is there a better way to do this?</p>