title: VBA loop preference
body: <p>I am currently trying to optimize a VBA code with extreme execution time (56 hours and more on high end PC). The main problem is nested loops (up to 8) due to complicated iterative calculations and referencing ranges in formulas.</p>
  <p>I have two questions:</p>
  <p><strong>1)</strong>
  What loops are most preferred in VBA (based on their hardware strain for lack of better word)</p>
  <p><strong>2)</strong>
  Is there a way to reference variable range of cells in a formula? For example in formula:</p>
  <pre><code>=AGGREGATE(15,6,SQRT((R2C10:R500000C10-RC[10])^2+(R2C11:R500000C11-RC[11])^2+(R2C12:R500000C12-RC[12])^2),1)
  </code></pre>
  <p>I am referencing relative cell: &quot;R500000C10&quot;. There are two main issues: For one, the half milion cells might not capture all data - though in most cases there is less active cells than that, in same cases that might be an issue. Second, it takes forever to scan through so many cells (same reference is used in multiple of VLOOKUPs).</p>
  <p>The first issue could be solved by referencing the whole column, but that makes the second problem worst.</p>
  <p>I would like to reference it in a similar way that xlDown works:</p>
  <pre><code>Range().End(xlDown).Select
  </code></pre>
  <p>The problem there is I would need to reference it within the Active.Cell.FormulaR1C1. Is there a way to do that?</p>
  <p>Thanks!</p>