title: VBA: Selecting array which depends on variables for use in lookup formula
body: <p>I am trying to write vba code that writes a formula to a cell.  The formula is to hlookup with the array being fixed, on another worksheet, and with size depending on variables already defined.</p>
  
  <p>Here is the relevant part of the code (the variables have already been defined and are integers):</p>
  
  <pre><code>Range("C2").Select
  ActiveCell.Formula = "=hlookup(A2,Visits!Range("C2",Cells(" &amp; rowsforlook &amp; ", " &amp; rowsforauto &amp; " + 1))," &amp; rowsforlook &amp; " - 1)"
  </code></pre>
  
  <p>I have checked that there are no problems with the variables.  I have realised that I haven't $fixed the array because I am not sure how to do this.  I also think the page reference "Visits!" is probably not correct, but the code did not select an array even without this.  The array I am trying to use is the selected region that would appear if I typed</p>
  
  <pre><code>Range("C2",Cells(" &amp; rowsforlook &amp; ", " &amp; rowsforauto &amp; " + 1)).Select
  </code></pre>
  
  <p>Thanks.</p>