selecting a range within a range
<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>