title: selecting a range within a range
body: <p>I am using following VBA code (MS Excel 2010) to select a range of cells within a given range, to copy and insert the copied  cells back into the source range:
  The range starts in the worksheet at row 2, down to row 2200 and from column 50 to 65.</p>
  
  <p><code>Set rngFEA = shtTarget.range("myrange")
  iMaxLines = 20
  With rngFEA
     .Range(Cells(3, 1), Cells(3 + iMaxLines, .Columns.Count)).Copy
      .Range(Cells(3, 1), Cells(3 + iMaxLines, .Columns.Count)).Insert Shift:=xlDown
   End With</code></p>
  
  <p>Doing it this way (no reference to rngFEA for the Cells(Row,Column) parameter) works fine, the selected cells are part of the range as expected.</p>
  
  <p>I don't like use no reference for the cells() parameter, because using no reference makes the cells referring to the worksheet and rather give erroneous results, so I would rather like to use rngFEA.cells():</p>
  
  <p><code>Set rngFEA = shtTarget.range("myrange")
  iMaxLines = 20
  With rngFEA
     .Range(.Cells(3, 1), .Cells(3 + iMaxLines, .Columns.Count)).Copy
      .Range(.Cells(3, 1), .Cells(3 + iMaxLines, .Columns.Count)).Insert Shift:=xlDown
   End With</code></p>
  
  <p>BUT the resulting range lies well OUTSIDE the range rngFEA, somewhere off to the left and down.
  I even could not find a relation between the used indexes and the resulting offset.</p>
  
  <p>I believe the range.insert could also be stated as</p>
  
  <pre><code>rngFEA.cells(3,1).insert shift:=xldown
  </code></pre>
  
  <p>but this is not my concern right now.</p>
  
  <p>I am well aware of the difference of referencing or not, but i do not understand why NOT using the reference to the range gives a correct result, and using the reference does not.</p>
  
  <p>I expect</p>
  
  <pre><code>rngFEA.range(rngFEA.cells(1,1), rngFEA.cells(10,10))
  </code></pre>
  
  <p>to return the range of the topmost, leftmost cell of the given range down to the tenth cell to the right and down in the same range.
  In the example code I select the leftmost cell in row 3 within the given range down to the 23rd cell and the right end of the range.
  (the actually selected row is row 3 in the workbook and hence row 2 in the range)
  I looked into Microsofts Informations and several forums, but could not find an explanation which describes this effect sufficiently.</p>
  
  <p>I know that</p>
  
  <pre><code>range.row 
  </code></pre>
  
  <p>returns the number of the row in the WORKSHEET where the range starts,</p>
  
  <pre><code>range.column 
  </code></pre>
  
  <p>returns the column where the range starts.</p>
  
  <p>Selecting a cell or a row within the given range</p>
  
  <pre><code>range.row(2)
  </code></pre>
  
  <p>does not return the second row of the range but the second row of the worksheet. A</p>
  
  <pre><code>for each myrow in range.rows
  </code></pre>
  
  <p>the index</p>
  
  <pre><code>myrow.row
  </code></pre>
  
  <p>returns the number of the row within the range, but using it as the selecting index seems to return the row within the worksheet, so I need to add</p>
  
  <pre><code>range.row + myrow.row
  </code></pre>
  
  <p>to index to the actual row within the range.</p>
  
  <p>The mechanics behind this and the above described behaviors of selecting a range within a range are confusing to me.
  Since there are many ways to handle things in Excel using VBA, I hope you can give me a general explanation of the described behavior rather than a solution (if not for explaining the why) :)</p>
  
  <p>THX in advance</p>
  
  <p>Ydalir</p>