选择范围内的范围

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.

我使用以下VBA代码(MS Excel 2010)在给定的范围内选择一个单元格，复制并将复制的单元格插入到源范围:从第2行的工作表开始，从第2行到第22行，从第50到第65列。

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

设置rngFEA = shtTarget.range(“myrange”)iMaxLines = 20，使用rngFEA .range(cell (3,1)， Cells(3 + iMaxLines， . columns.count)。复制。range (cell(3,1)、Cells(3 + iMaxLines， . columns . count)。插入转变:= xlDown结尾

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.

这样做(对于单元格(行、列)参数没有对rngFEA的引用)很有效，所选择的单元格是预期范围的一部分。

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():

我不喜欢对单元格()参数不使用引用，因为不使用引用会使单元格指向工作表，而产生错误的结果，所以我宁愿使用rngFEA.cells():

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

设置rngFEA = shtTarget.range(“myrange”)单元格(3,1)，.Cells(3 + iMaxLines， . columns . count)。复制.Range(。单元格(3,1)，.Cells(3 + iMaxLines， . columns . count)。插入转变:= xlDown结尾

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.

但是结果的范围远远超出了rngFEA范围，在左边和下面的某个地方。我甚至找不到使用的索引和结果偏移量之间的关系。

I believe the range.insert could also be stated as

我相信范围。插入也可以声明为。

rngFEA.cells(3,1).insert shift:=xldown

but this is not my concern right now.

但这不是我现在所关心的。

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.

我很清楚引用与否的区别，但我不明白为什么不使用对范围的引用会得到正确的结果，而使用引用则不会。

I expect

我希望

rngFEA.range(rngFEA.cells(1,1), rngFEA.cells(10,10))

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.

返回给定范围的最上面、最左边的单元格的范围，并将范围缩小到右边的第十单元格，并在相同的范围内缩小。在示例代码中，我选择在给定范围内的第3行中最左边的单元格，直到第23个单元格和范围的右端。(实际选择的行是工作簿中的第3行，因此是范围中的第2行)

I know that

我知道

range.row

returns the number of the row in the WORKSHEET where the range starts,

返回工作表中开始范围的行数，

range.column

returns the column where the range starts.

返回范围开始的列。

Selecting a cell or a row within the given range

选择给定范围内的单元格或行

range.row(2)

does not return the second row of the range but the second row of the worksheet. A

不返回范围的第二行，而是工作表的第二行。一个

for each myrow in range.rows

the index

该指数

myrow.row

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

返回范围内的行数，但使用它作为选择索引似乎会返回工作表中的行，因此需要添加

range.row + myrow.row

to index to the actual row within the range.

索引到该范围内的实际行。

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) :)

这和上面所描述的在范围内选择范围的行为背后的机制让我感到困惑。由于使用VBA在Excel中处理事情的方法很多，我希望您能对所描述的行为给出一个大概的解释，而不是一个解决方案(如果不是解释原因的话):

THX in advance

谢谢提前

Ydalir

2 个解决方案

#1

Can confirm this behavior:

可以确认这种行为:

Sub Tester()

    Dim rng As Range

    Set rng = Range("C3:H28")

    'This selects E5:F6 (???)
    With rng
        .Range(.Cells(1, 1), .Cells(2, 2)).Select
    End With


    'This selects C3:D4 (expected)
    With rng
        rng.Parent.Range(.Cells(1, 1), .Cells(2, 2)).Select
    End With

End Sub

Seems like it may be related to the "double relative" combination of using both .Range and .Cells

似乎它可能与同时使用.Range和. cell的“双相对”组合有关

Instead using rng.Parent.Range and having only the .Cells be relative to the containing range seems to fix it (and still allows for fully-qualified range references)

而不是使用rng.Parent。范围和只有.Cells相对于包含范围似乎可以修复它(并且仍然允许完全限定范围引用)

#2

I encountered the same behavior when getting an Excel range within a range in VB.Net. Tim's answer solved the weird behavior. At first I though it had something to do with the use of With but I guess it had something to do with the double relative referencing of dot notation as Tim had suggested.

在VB.Net中获得一个范围内的Excel范围时，我遇到了相同的行为。蒂姆的回答解决了这个奇怪的行为。一开始，我认为这和使用with有关，但我猜这和点符号的双重相对引用有关，正如蒂姆所建议的。

Public Sub SomeMergingFunction(ByRef inputRange As Excel.Range)
    With inputRange
        Debug.Print(.Address) ' $A$4:$A$130 correct
        Debug.Print(.Cells(1, 1).Address) ' $A$4 correct
        Debug.Print(.Range(.Cells(1, 1), .Cells(1, 1)).Address) ' $A$7 wrong
        Debug.Print(.Parent.Range(.Cells(1, 1), .Cells(1, 1)).Address) ' $A$4 correct
    End With
End Sub

#1