在一个变量中循环遍历两个范围

问题描述：

在一个变量中遍历两个范围.

Looping through two ranges in one variant variable.

我正在尝试使用一个变体同时读取两个范围.我有两个范围A和B，并且正在合并它们.合并这两个范围后，我将使用一种变体来读取它.我的变量变量仅读取A列，而忽略B列.任何建议我做错了.

I am trying to read two ranges together at the same time using one variant. I have two ranges A and B, and I am combining them. After combining these two ranges, I am using a variant to read it. My variant variable only reading column A and ignoring Column B. Any suggestion what I am doing wrong.

Dim rngText as Range, rngText2 as Range, results as Range, dText
   Set rngText = wSheet3.Range(wSheet3.Range("A1"), wSheet3.Cells(Rows.Count, 1).End(xlUp))
   Set rngText2 = wSheet3.Range(wSheet3.Range("B1"), wSheet3.Cells(Rows.Count, 2).End(xlUp))
   Set results = Union(rngText, rngText2)
   dText = results.Value

        For i = 1 to Ubound(dText,1)
          'other condition here.... 

        Next i

答

For i = 1 to Ubound(dText,1)

此循环迭代 dText 的第一个维度，该维度被声明为隐式的 Variant .

This loop iterates the first dimension of dText, which is declared as an implicit Variant.

dText = results.Value

这为 Variant 分配了一个二维数组，该数组表示 Union 操作的结果.除非 Range 表示单个单元格，否则 Range.Value 始终返回2D数组.

This assigns the Variant with a 2D array representing the result of the Union operation. Unless a Range is representing a single cell, Range.Value always returns a 2D array.

您需要一个嵌套循环来迭代2D数组的两个维度.

You need a nested loop to iterate both dimensions of your 2D array.

Dim currentRow As Long
For currentRow = 1 To UBound(dText, 1)
    Dim currentCol As Long
    For currentCol = 1 To UBound(dText, 2)
        ' do stuff
    Next
Next

根据您要实现的目标，最好只迭代行，并让循环主体的逻辑获取列索引:

Depending on what you're trying to achieve, it might be better to only iterate rows, and have your loop body's logic get the column indices:

Dim currentRow As Long
For currentRow = 1 To UBound(dText, 1)
    Debug.Print "Column A: " & dText(currentRow, 1), "Column B: " & dText(currentRow, 2)
Next

请注意，二维数组包含表示单元格中保存的任何值/类型的 Variant 值:如果单元格包含数字，则数组索引将指向某些 Variant/Double ;如果单元格包含字符串，则数组索引将指向某个 Variant/String ；如果单元格包含错误，则数组索引将指向某些 Variant/Error -最后一点很关键:您需要先验证单元格值是否不包含错误.假定其类型并对它进行任何操作(例如，在任一列中给定 Variant/Error 值的情况下，上述字符串连接都会因运行时错误13/类型不匹配"而失败，因为不能将String 与 Error 进行比较或以其他方式(隐式或显式地)转换为 Error .这样会更安全:

Note that the 2D array holds Variant values representing whatever value/type that's held in the cells: if a cell contains a number, the array index will point to some Variant/Double; if a cell contains a string, the array index will point to some Variant/String; if a cell contains an error, the array index will point to some Variant/Error - and that last point is critical: you'll want to validate that the cell value doesn't contain an error before you assume its type and do anything with it (e.g. the above string-concatenation would fail with run-time error 13 / "type mismatch" given a Variant/Error value in either column, because a String can't be compared to or otherwise converted [implicitly or explicitly] to an Error). This would be safer:

Dim currentRow As Long
For currentRow = 1 To UBound(dText, 1)
    If Not IsError(dText(currentRow, 1) And Not IsError(dText(currentRow, 2)) Then
        Debug.Print "Column A: " & dText(currentRow, 1), "Column B: " & dText(currentRow, 2)
    End If
Next

在一个变量中循环遍历两个范围

相关推荐