vba在不连续的范围内循环 [英] vba Loop over a non-contiguous range

问题描述

我在行上有一个不连续的范围（myRange的示例地址：$ 2：$ 2，$ 4：$ 205，$ 214：$ 214），我想访问该范围内的特定行和列。我尝试了以下内容：

'获取第二行的值，范围内的第一列

  myRange.rows（2）.Cells（，1）.Value 
  

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

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

谢谢

解决方案

p>这是我的条目 - 不一定比Irwin的

 函数GetValue（rInput As Range，Row As Long，Column As Long ）As Variant 
 
 Dim rArea As Range 
 Dim lCumRows As Long 
 Dim lActualRow As Long 
 
对于每个rArea在rInput.Areas 
 lCumRows = lCumRows + rArea.Rows.Count 
如果Row< = lCumRows Then 
 lActualRow = rArea.Rows（1）.Row +（Row  - （lCumRows  -  rArea.Rows.Count + 1）） 
退出
结束如果
下一个rArea 
 
如果lActualRow> 0然后
 GetValue = rInput.Parent.Cells（lActualRow，Column）.Value 
 End If 
 
结束函数
 
函数GetValue2（rInput As Range ，Row As Long，Column As Long）As Variant 
 
 Dim rRow As Range 
 Dim lRowCnt As Long 
 
对于每个rRow在rInput.Rows 
 lRowCnt = lRowCnt + 1 
如果lRowCnt = lrow然后
 GetValue2 = rRow.Cells（1，Column）.Value 
退出
结束如果
下一个rRow 
 
结束功能
  

然后去阅读 http://www.dailydoseofexcel.com/archives/2004/07/07/the-strange-object / ，以了解为什么Excel的行为方式。

如果您有兴趣，测试过程

  Sub test（）
 
 Dim myRange As Range 
 
设置myRange = Union（Rows（2））Range （4:20 5），Rows（214））
 
 Debug.Print GetValue（myRange，1，2），GetValue（myRange，1，2）
 Debug.Print GetValue（myRange， 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 
  

I have a non-contiguous range on rows (example address of myRange: $2:$2,$4:$205,$214:$214) and I would like to access a specific row and column within the range. I have tried the following:

'Get the value of the 2nd row, 1st column within the range

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

However, this is giving me the value of the 2nd row in the WorkSheet, and NOT in the range - meaning it is giving me address $3$1 - and not $4$1

Can someone please explain how I can access the values within in my range? (It may have to do with different areas)

Thank You

解决方案

Here are my entries - not necessarily better than Irwin's

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

And go read http://www.dailydoseofexcel.com/archives/2004/07/07/the-strange-object/ for some insight as to why Excel is behaving that way.

And the test proc if you're interested

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

这篇关于vba在不连续的范围内循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！