title: Copy formula down a column until the last row without using dim
body: <p>I have an Excel VBA macro that does a lot of vlookups and I need to copy the formula down the column. If I record the macro then it will only copy down to the same amount of rows. I run this file every week and the number of rows will always be different. I edit the VBA code to copy down the column but it only works once, then the other columns will get an error. Here is part of my code:</p>
  
  <pre><code> ActiveCell.FormulaR1C1 = _
      "=IF(NOT(ISERROR(VLOOKUP(RC[-23],'[GEM Crossover.xlsx]GEM_Crossover'!C1:C2,2,FALSE))),VLOOKUP(RC[-23],'[GEM Crossover.xlsx]GEM_Crossover'!C1:C2,2,FALSE),IF(NOT(ISERROR(VLOOKUP(CONCATENATE(RC[-16],LEFT(RC[-14],1)),'[PRIVATE SWITCH.xlsx]Sheet1'!C1:C3,2,FALSE))),VLOOKUP(CONCATENATE(RC[-16],LEFT(RC[-14],1)),'[PRIVATE SWITCH.xlsx]Sheet1'!C1:C2,2,FALSE),IF(AND(LEFT(RC[-14],1)=""P"",RC[-12]=""I""),RC[-16],IF(AND(LEFT(RC[-14],1)=""P"",RC[-12]=""X""),RC[-16],RC[-14]))))"
  Range("X2").Select
  Selection.AutoFill Destination:=Range("X2:X" &amp; Cells(Rows.Count, 2).End(xlUp).Row)
  Columns("X:X").Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Application.CutCopyMode = False
  </code></pre>
  
  <p>The code above works perfectly fine, but how come it doesn't not work when I use this code for another column?:</p>
  
  <pre><code>Range("B2").Select
  ActiveCell.FormulaR1C1 = "=RC[-1]&amp;RC[2]"
  Range("B2").Select
  Range("B2").AutoFill Destination:=Range("B2:B" &amp; Cells(Rows.Count, 2).End(xlUp).Row)
  Columns("B:B").Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  </code></pre>
  
  <p>I set the autofill destination to the same as the first code. I just changed the columns from X to B and it won't work. Did I do something wrong?</p>