VBA中具有多个非顺序列的范围索引 [英] Index of range with multiple non-sequential columns in VBA

问题描述

考虑以下VBA MWE

Consider the following VBA MWE

Sub test()

Dim rng As Range
Set rng = Range("A:A, C:C")

Dim rRow As Range

For i = 1 To 5
    Set rRow = Intersect(rng, rng.Cells(i, 1).EntireRow)
    rRow.Value = 1
    rRow.Cells(, 2).Value = 2
Next
End Sub

哪个产生一个这样的输出

Which produces an output that looks like this

1   2   1
1   2   1
1   2   1
1   2   1
1   2   1

我们可以看到，行 rRow.Value = 1 设置单元格第一和第三列为1.现在，我不知道为什么 rRow.Cells（1,2）不访问第三列，以便输出是

As we can see, the line rRow.Value = 1 sets the cells in the first and third column to 1. Now, I can't get my head around why the rRow.Cells(1,2) doesn't access the third column such that the output is

1       2
1       2
1       2
1       2
1       2

并将第二列留空，因为这似乎是行中发生的 rRow.Value = 1 。有人可以向我解释这个逻辑吗？

and leave the second column empty, since this appears to be what is happening in the line rRow.Value = 1. Can someone explain this logic to me?

编辑：

注释 rRow .Cells（，2）.Value = 2 ，使代码读取

Sub test()

Dim rng As Range
Set rng = Range("A:A, C:C")

Dim rRow As Range

For i = 1 To 5
    Set rRow = Intersect(rng, rng.Cells(i, 1).EntireRow)
    rRow.Value = 1
    'rRow.Cells(, 2).Value = 2
Next


End Sub

产生以下输出

1       1
1       1
1       1
1       1
1       1

其中列A和C填充一个，列B留下单独。

where columns A and C are filled with ones, and column B is left alone.

推荐答案

使用范围或 / code>属性范围对象（而不是更常见的工作表），提供了一个引用相对于原始范围的左上单元格的范围。它不以任何方式限制在原始范围内的细胞。
因此：

Using the Range or Cells property of a Range object (rather than the more usual Worksheet), provides a reference to a range relative to the top left cell of the original range. It is not in any way restricted to cells within that original range. Hence this:

Range("A1").Range("B2")

指单元格右侧的一列，A1下方的一行。这样做：

refers to the cell one column to the right and one row below A1. So does this:

Range("A1:A10").Range("B2")

注意，它仍然只是指一个单元格，即使原始范围是10个单元格。 单元格的工作方式完全相同（就像使用工作表父代）一样。所以这样：

Note that it still only refers to one cell, even though the original range was 10 cells. Cells works in exactly the same way (just as it does with a Worksheet parent). So this:

Range("A1").Cells(2, 2)

，这样：

Range("A1:A10").Cells(2, 2)

均指B2。

这篇关于VBA中具有多个非顺序列的范围索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！