title: VBA Excel - Extend Formulas Down (Complicated)
body: <p><strong>Scenario:</strong></p>
  
  <p>I have a consistent formula starting in <code>Range("V4")</code>.</p>
  
  <p>This range has a <em>Defined Name</em>: <strong>Stock4</strong> (This is so the formula/cell can be relatively referenced if the user adds or removes surrounding columns).</p>
  
  <p>The spreadsheet is constantly updated with fresh data (Pasted in <code>Range("A4:U4")</code>).</p>
  
  <p>The amount of pasted rows varies with each batch of data.</p>
  
  <p>I need to extend the formula within <strong>Stock4</strong> down to the last row...</p>
  
  <p><strong>What I'm doing:</strong></p>
  
  <p>So, I'm using a combination of...</p>
  
  <pre><code>Dim Lastrow As Integer
  Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
  </code></pre>
  
  <p>And</p>
  
  <pre><code>.AutoFill Destination:=Range(*insert range here*), Type:=xlFillDefault
  </code></pre>
  
  <p>And my cell's <em>Defined Name</em>: <strong>Stock4</strong>.</p>
  
  <p><strong>Here is what I get:</strong></p>
  
  <pre><code>Range("Stock4").AutoFill Destination:=Range("Stock4:$V" &amp; Lastrow), Type:=xlFillDefault
  </code></pre>
  
  <p>This works very well, however...</p>
  
  <pre><code>$V
  </code></pre>
  
  <p>...is not relative.</p>
  
  <p><strong>My attempted solutions:</strong></p>
  
  <p>I have tried using <code>Split(ActiveCell.Address(1, 0), "$")(0) &amp; Lastrow</code></p>
  
  <p>This returns the <code>ActiveCell</code> <em>Column Letter</em>: <strong>V</strong> and <code>Lastrow</code> <em>Row Number</em>: <strong>4831</strong>.</p>
  
  <p>However...</p>
  
  <p>I can't simply substitute this code into my existing code in place of <code>$V" &amp; Lastrow</code> (<em>see below</em>)</p>
  
  <pre><code>Range("Stock4").AutoFill Destination:=Range("Stock4:Split(ActiveCell.Address(1, 0), "$")(0) &amp; Lastrow"), Type:=xlFillDefault
  </code></pre>
  
  <p><em>Syntax errors aside, this simply won't work</em>.</p>
  
  <p><strong>Question:</strong></p>
  
  <p>How can I extend the formula located in <strong>Stock4</strong> down to the last row of <em>Stock4's</em> column (relative reference) using <code>VBA code</code>.</p>
  
  <p>(This <code>code</code> is then going to be adapted and repeated for multiple relative columns across multiple spreadsheets)</p>
  
  <p><strong>To sum up:</strong></p>
  
  <p>Extend Formulas Down To Lastrow Using Defined Name Ranges And Relative Columns.
  (I have spent the best part of 2 working days going grey with this problem - Is it possible? <strong>It must be possible!</strong>)</p>