在“范围”中遍历“单元格” [英] Traversing `Cells` in a `Range`

问题描述

问题的简短版本：

这里的代码

  Dim rng As Range 
设置rng =选择
设置rng = rng.Columns（1）
对于每个cl在rng 
 cl.Select' <  -  Break＃2 
  

当我们选择 A1：B37

 ？ rng.address（外部= =）
 [Book2] Sheet1！$ A $ 1：$ A $ 37 
 
？ cl.Address（外部：= True）
 [Book2] Sheet1！$ A $ 1：$ A $ 37 
  

任何人都可以帮助我理解为什么 cl - > A1：A37 而不是 cl - > A1 ？
请注意，我想象重写代码以获得预期的结果。但是我想知道这个问题是什么，可能会学到新的东西。这是问题的关键。

---

长版本的问题）

我有一个子例程，它在所选（矩形）范围 rng 上工作。这里的相关代码如下所示。它根据 rng 列的数字 ncols 分支。

当 ncols = 1 时，它循环遍历 rng 中的每个单元格 cl code>，选择 cl 并执行一些操作。
当起始选择是 A1：A37 时，这样可以正常工作，如直接进入循环后立即窗口中的输出所示。 1 （见下面的代码）

 ？ rng.address（外部= =）
 [Book2] Sheet1！$ A $ 1：$ A $ 37 
 
？ cl.Address（External：= True）
 [Book2] Sheet1！$ A $ 1 
  

当 ncols<> 1 时，我想循环遍历第一列中的每个单元格 cl / em> rng ，做以前一样。
现在当起始选择是 A1：B37 时，这不起作用，如在<＃> Break＃2

 ？ rng.address（外部= =）
 [Book2] Sheet1！$ A $ 1：$ A $ 37 
 
？ cl.Address（外部：= True）
 [Book2] Sheet1！$ A $ 1：$ A $ 37 
  

任何人都可以帮助我理解为什么这里 cl - > A1：A37 而不是 cl - > A1 （如 Break＃1 ）？
请注意，我想象重写代码以获得预期的结果。但是我想知道这个问题是什么，可能会学到新的东西。这是什么问题。

  Dim rng As Range 
设置rng =选择
 Dim ncols As Long 
 ncols = rng.Columns.Count 
 Dim cl As Range 
'1-如果只选择一列，... 
 If（ncols = 1）然后
对于每个cl在rng 
 cl.Select'<  -  break＃1 
 ... 
下一个cl 
'2-如果超过选择一列... 
 Else 
设置rng = rng.Columns（1）
对于每个cl在rng 
 cl.Select'<  -  Break＃2 
 Dim rng2 As Range 
 Set rng2 = Range（cl，cl.Offset（0，ncols  -  1））
 rng2.Select 
 ... 
下一个cl 
如果
  




解决方案

我没有有机会测试你的代码，但你可能只是缺乏明确的缺点： cl 是一个范围，所以列和行和区域和其他任何排序范围类型的对象。您可以使用范围迭代器，如 cl ：对于Rng.Rows 或中的每个cl。 ..在rng.Columns 或 ... rng.Cells 等。

换句话说，虽然你可能期待 cl 成为一个单元格范围，可能不是这样，除非你明确地，如：

 对于rng.Cells中的每个cl 
  

或者，由于您将其定义为单列，这将是相当的：

 code>对于rng.Rows中的每个cl 
  

（技术上， cl 表示 rng 中的行范围，但由于它是单列范围，每个行是单一的单元格）。

您的代码可以非常简化：

  Sub f（）
 
 Dim rng As Range 
 Dim cl As Range 
 Dim rng2 As Range 
 
 Set rng = Range（Selection。地址）.Resize（，1）
 ncols =范围（Selection.Address）.Columns.Count 
 
对于每个cl在rng.Cells 
 cl.Select'<  -  Break＃2 
 
如果nCols > 1然后
设置rng2 =范围（cl，cl.Offset（0，ncols  -  1））
 rng2.Select 
'... 
结束如果
下一步cl 
 
 End Sub 
  

Short version of the question:

The code here

Dim rng As Range
Set rng = Selection
Set rng = rng.Columns(1)
For Each cl In rng
  cl.Select    ' <-- Break #2

gives me this in the immediate window when the selection is A1:B37

? rng.address(External:=True)
[Book2]Sheet1!$A$1:$A$37

? cl.Address(External:=True)
[Book2]Sheet1!$A$1:$A$37

Anyone can help me understanding why cl -> A1:A37 instead of cl -> A1? Note that I imagine rewriting code to get the intended results. But I would like to know what is the problem, and probably learn something new along. This is what the question is about.

---

Long version of the question (as originally posted):

I have a subroutine, which works on the selected (rectangular) range rng. The code of relevance here is shown below. It branches depending on the number ncols of columns of rng.

When ncols=1, it loops through each cell cl in rng, selecting cl and performing some actions. When the starting selection is A1:A37, this works ok, as shown by the output in the immediate window right after entering the loop at Break #1 (see code below)

? rng.address(External:=True)
[Book2]Sheet1!$A$1:$A$37

? cl.Address(External:=True)
[Book2]Sheet1!$A$1

When ncols<>1, I want to loop through each cell cl in the first column of rng, doing the same as before. Now when the starting selection is A1:B37, this does not work, as shown by the output in the immediate window at Break #2

? rng.address(External:=True)
[Book2]Sheet1!$A$1:$A$37

? cl.Address(External:=True)
[Book2]Sheet1!$A$1:$A$37

Anyone can help me understanding why here cl -> A1:A37 instead of cl -> A1 (as in Break #1)? Note that I imagine rewriting code to get the intended results. But I would like to know what is the problem, and probably learn something new along. This is what the question is about.

Dim rng As Range
Set rng = Selection
Dim ncols As Long
ncols = rng.Columns.Count
Dim cl As Range
' 1- If only one column is selected, ...
If (ncols = 1) Then
  For Each cl In rng
    cl.Select    ' <-- Break #1
    ...
  Next cl
' 2- If more than one column is selected, ...
Else
  Set rng = rng.Columns(1)
  For Each cl In rng
    cl.Select    ' <-- Break #2
    Dim rng2 As Range
    Set rng2 = Range(cl, cl.Offset(0, ncols - 1))
    rng2.Select
    ...
  Next cl
End If

解决方案

I have not had a chance to test your code yet but you may simply be suffering from lack of explicity: cl is a Range, so is a Column and a Row and an Area and any other sort of range-type object. You can use a range iterator like cl : For each cl in Rng.Rows or ...in rng.Columns, or in ...rng.Cells, etc.

In other words, while you may be expecting cl to be a cell range, that may not be the case unless you make it explicit, like:

For each cl in rng.Cells

Or, since you are defining it as a single-column, this would be equivalent:

For Each cl in rng.Rows

(technically, cl represents a row range in that rng, but since it's a single column range, each "row" is a single cell, too).

Your code can acutally be quite streamlined:

Sub f()

    Dim rng As Range
    Dim cl As Range
    Dim rng2 As Range

    Set rng = Range(Selection.Address).Resize(, 1)
    ncols = Range(Selection.Address).Columns.Count

    For Each cl In rng.Cells
      cl.Select    ' <-- Break #2

      If nCols > 1 Then
          Set rng2 = Range(cl, cl.Offset(0, ncols - 1))
          rng2.Select
          '...
      End If
    Next cl

End Sub

这篇关于在“范围”中遍历“单元格”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！