范围，单元格(和项目)在非连续范围内 [英] Range, Cells (and Item) in non sequential ranges

问题描述

我在此处

  Sub test()Debug.Print"With Cells(2)"Debug.Print Intersect(Range("B:B，C:C")，Rows(1)).Cells(2).Address'prints $ C $ 1Debug.Print Intersect(Range("B:B，D:D")，Rows(1)).Cells(2).Address'prints $ B $ 2Debug.Print Range("B:B，C:C").Cells(2).Address'打印$ B $ 2Debug.Print Range("B:B，D:D").Cells(2).Address'打印$ B $ 2Debug.Print"With Item(2)"Debug.Print Intersect(Range("B:B，C:C")，Rows(1)).Item(2).Address'prints $ C $ 1Debug.Print Intersect(Range("B:B，D:D")，Rows(1)).Item(2).Address'prints $ B $ 2Debug.Print Range("B:B，C:C").Item(2).Address'prints $ B $ 2Debug.Print Range("B:B，D:D").Item(2).Address'打印$ B $ 2结束子 

解决方案

许多VBA Range方法可以在连续范围内完美工作，但是当范围具有多个区域时，它们具有特殊的行为，也就是说，它们适用于VBA的第一个区域范围.单元格(i)就是其中之一，尽管在以 myRange中的每个单元格的顺序用循环来跨范围时，即使范围具有多个区域，它们也将正常工作并跨过所有单元格./p>

在测试的第一行中，Intersect方法返回了一个连续范围B1:C1，因此自然而然，项目2为C1.因为具有一个参数的单元格(i)从左到右逐行跨越区域.

在第二行中，Intersect方法返回了一个多区域范围:(B1，D1).因此Cells(i)仅适用于第一个区域B1.

ps:Range.Cells()以此方式工作时，不会检查返回的单元格是否属于原始范围.

编辑:要回答第三行

在第三行中，(B:B，C:C)的定义是一个多区域范围，第一个区域是B行，第二个区域是C行.对于人类来说，这等同于(B:C)，但不适用于编译器.定义范围时，编译器不会尝试合并区域，因为在一般情况下，这种操作的计算量很大并且很困难.它只是不尝试在定义范围时自动合并区域，而是遵循程序员的定义.我更喜欢这样:遵循程序员的逻辑并保持快速.

I was looking at Holene question here and while trying to understand the MSDN documentation I came into the following problem. could some one explain why the following lines give two different results?

Sub test()
    Debug.Print "With Cells(2)"
    Debug.Print Intersect(Range("B:B, C:C"), Rows(1)).Cells(2).Address 'prints $C$1
    Debug.Print Intersect(Range("B:B, D:D"), Rows(1)).Cells(2).Address 'prints $B$2
    Debug.Print Range("B:B, C:C").Cells(2).Address 'prints $B$2
    Debug.Print Range("B:B, D:D").Cells(2).Address 'prints $B$2
    Debug.Print "With Item(2)"
    Debug.Print Intersect(Range("B:B, C:C"), Rows(1)).Item(2).Address 'prints $C$1
    Debug.Print Intersect(Range("B:B, D:D"), Rows(1)).Item(2).Address 'prints $B$2
    Debug.Print Range("B:B, C:C").Item(2).Address 'prints $B$2
    Debug.Print Range("B:B, D:D").Item(2).Address 'prints $B$2
End Sub

解决方案

Many VBA Range methods work perfectly on a contiguous range, but have special behavior when the range has multiple areas, that is, they apply to the first area of the range. Cells(i) is one of them, although when spanning the range with a loop in the sort For Each cell in myRange they will work correctly and span all the cells even if the range has multiple areas.

In the first line of your test, the Intersect method has returned a contiguous range B1:C1, so naturally, Item 2 is C1. Because the cells(i) with one parameter spans the area row by row from left to right.

In the second line, the Intersect method has returned a multiple-area range: (B1, D1). so Cells(i) applies only on the first area, which is B1.

ps: Range.Cells(), while working this way, does not check whether the returned cell belongs to the original range.

Edit: To answer about the third line

In the third line, The definition of (B:B, C:C) is a multiple-area range, first area is row B, second area is Row C. For a human this would be equivalent to (B:C), but not for the compiler. The compiler does not try to merge areas when a range is defined, because such operation is computationally intensive and difficult in the general case. It just doesnt try to merge areas automatically when a range is defined, it follows the programmer's definition. I prefer it like that: follow the programmer's logic and stay fast.

这篇关于范围，单元格(和项目)在非连续范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！