vba 在非连续范围内循环

我的行有一个不连续的范围（myRange 的示例地址：$2:$2,$4:$205,$214:$214），我想访问该范围内的特定行和列。我已经尝试过以下方法：

'获取范围内第2行第1列的值

myRange.rows(2).Cells(, 1).Value

但是，这给了我工作表中第二行的值，而不是在范围内 - 这意味着它给了我地址 $3$1 - 而不是 $4$1

有人可以解释一下我如何访问我范围内的值吗？ （可能与不同地区有关）

谢谢

这是我的条目 - 不一定比欧文的好

Function GetValue(rInput As Range, Row As Long, Column As Long) As Variant

    Dim rArea As Range
    Dim lCumRows As Long
    Dim lActualRow As Long

    For Each rArea In rInput.Areas
        lCumRows = lCumRows + rArea.Rows.Count
        If Row <= lCumRows Then
            lActualRow = rArea.Rows(1).Row + (Row - (lCumRows - rArea.Rows.Count + 1))
            Exit For
        End If
    Next rArea

    If lActualRow > 0 Then
        GetValue = rInput.Parent.Cells(lActualRow, Column).Value
    End If

End Function

Function GetValue2(rInput As Range, Row As Long, Column As Long) As Variant

    Dim rRow As Range
    Dim lRowCnt As Long

    For Each rRow In rInput.Rows
        lRowCnt = lRowCnt + 1
        If lRowCnt = lrow Then
            GetValue2 = rRow.Cells(1, Column).Value
            Exit For
        End If
    Next rRow

End Function

然后去读书http://www.dailydoseofexcel.com/archives/2004/07/07/the-strange-object/ http://www.dailydoseofexcel.com/archives/2004/07/07/the-strange-object/了解 Excel 为何会出现这种行为。

如果你有兴趣的话还有测试过程

Sub test()

    Dim myRange As Range

    Set myRange = Union(Rows(2), Range("4:205"), Rows(214))

    Debug.Print GetValue(myRange, 1, 2), GetValue(myRange, 1, 2)
    Debug.Print GetValue(myRange, 2, 2), GetValue(myRange, 2, 2)
    Debug.Print GetValue(myRange, 3, 2), GetValue(myRange, 3, 2)
    Debug.Print GetValue(myRange, 200, 2), GetValue(myRange, 200, 2)

End Sub