vba 在非连续范围内循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2568141/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba Loop over a non-contiguous range
提问by Jeffrey
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:
我有一个不连续的行范围（myRange 的示例地址：$2:$2,$4:$205,$214:$214），我想访问该范围内的特定行和列。我尝试了以下方法：
'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
但是，这给了我工作表中第二行的值，而不是在范围内 - 这意味着它给了我地址 $3$1 - 而不是 $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
谢谢你
回答by Dick Kusleika
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.
并阅读http://www.dailydoseofexcel.com/archives/2004/07/07/the-strange-object/以了解为什么 Excel 会这样。
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
回答by Irwin M. Fletcher
I think what you are wanting VBA to do is to see your non-contiguous range as a contiguous one. I don't think the approach that you are taking will work. You will have to treat this like multipe contiguous ranges. The following code should get you started. Where rowSelection is the row in your range that you are interested in. If you enter 2, it will select row 4 in the workbook as it is the second row in your range.
我认为您希望 VBA 做的是将非连续范围视为连续范围。我认为您采用的方法行不通。您必须将其视为多个连续范围。以下代码应该可以帮助您入门。其中 rowSelection 是您感兴趣的范围中的行。如果输入 2，它将选择工作簿中的第 4 行，因为它是您范围中的第二行。
Sub Macro1()
Dim rowCounter As Long
Dim rowSelection As Long
rowSelection = 2
For Each Rng In Range("A2:A2,A4:A205,A214:A214").Areas
If Rng.Rows.Count >= rowSelection Then
Rng.Rows(rowSelection - rowCounter).Cells(1, 1).Select
End
Else
rowCounter = rowCounter + Rng.Rows.Count
End If
Next Rng
End Sub
回答by will
This code iterates through a named range:
此代码遍历命名范围：
Dim c As Range
x=0
For Each c In Range("MyNamedRange")
'if x = pick a number and do something here
MsgBox c.Address & vbTab & c.Value
x=x+1
Next c
回答by Jeffrey
Thank you everyone for their answers -- Before saw these answers I figured it out myself and so far it is working. I wont say it is the most efficent method but seems to work:
谢谢大家的回答——在看到这些答案之前，我自己弄明白了，到目前为止它是有效的。我不会说这是最有效的方法，但似乎有效：
Public Function NextRow(index As Integer, rows As Range) As Range
Dim i As Integer, r As Range
i = 1
Set NextRow = Nothing
For Each r In rows.rows
If i = index Then
Set NextRow = Range(r.Address)
Debug.Print "NextRow: " & NextRow.Address
Exit Function
End If
i = i + 1
Next r
End Function
It seems similar 2nd answer - baically I am advancing to range to index I want to work with and than I return a range set by the address (!important)
这似乎与第二个答案相似 - 基本上我正在推进到我想要使用的索引范围，然后返回由地址设置的范围（！重要）
I than call it like this:
我不是这样称呼它的：
NextRow(2, myRange).Cells(,1).value
