vba 循环遍历 Excel 中某个范围的每一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1463236/
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
Loop through each row of a range in Excel
提问by Margaret
This is one of those things that I'm sure there's a built-in function for (and I may well have been told it in the past), but I'm scratching my head to remember it.
这是我确信有一个内置函数的事情之一（过去我很可能已经被告知过），但我正在挠头记住它。
How do I loop through each row of a multi-column range using Excel VBA? All the tutorials I've been searching up seem only to mention working through a one-dimensional range...
如何使用 Excel VBA 遍历多列范围的每一行？我一直在搜索的所有教程似乎只提到在一维范围内工作......
回答by Mike
Dim a As Range, b As Range
Set a = Selection
For Each b In a.Rows
MsgBox b.Address
Next
回答by David Andres
Something like this:
像这样的东西：
Dim rng As Range
Dim row As Range
Dim cell As Range
Set rng = Range("A1:C2")
For Each row In rng.Rows
For Each cell in row.Cells
'Do Something
Next cell
Next row
回答by tc_NYC
Just stumbled upon this and thought I would suggest my solution. I typically like to use the built in functionality of assigning a range to an multi-dim array (I guess it's also the JS Programmer in me).
只是偶然发现了这一点，并认为我会建议我的解决方案。我通常喜欢使用为多维数组分配范围的内置功能（我猜这也是我的 JS 程序员）。
I frequently write code like this:
我经常写这样的代码：
Sub arrayBuilder()
myarray = Range("A1:D4")
'unlike most VBA Arrays, this array doesn't need to be declared and will be automatically dimensioned
For i = 1 To UBound(myarray)
For j = 1 To UBound(myarray, 2)
Debug.Print (myarray(i, j))
Next j
Next i
End Sub
Assigning ranges to variables is a very powerful way to manipulate data in VBA.
为变量分配范围是在 VBA 中操作数据的一种非常强大的方法。
回答by LimaNightHawk
In Loops, I always prefer to use the Cellsclass, using the R1C1 reference method, like this:
在循环中，我总是喜欢使用Cells类，使用 R1C1 引用方法，如下所示：
Cells(rr, col).Formula = ...
This allows me to quickly and easily loopover a Rangeof cells easily:
这使我能够快速，轻松地循环在范围很容易细胞：
Dim r As Long
Dim c As Long
c = GetTargetColumn() ' Or you could just set this manually, like: c = 1
With Sheet1 ' <-- You should always qualify a range with a sheet!
For r = 1 To 10 ' Or 1 To (Ubound(MyListOfStuff) + 1)
' Here we're looping over all the cells in rows 1 to 10, in Column "c"
.Cells(r, c).Value = MyListOfStuff(r)
'---- or ----
'...to easily copy from one place to another (even with an offset of rows and columns)
.Cells(r, c).Value = Sheet2.Cells(r + 3, 17).Value
Next r
End With
