vba 循环遍历 2 列范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16449083/
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
looping through a range of 2 columns
提问by lalachka
My question has been answered over and over but I don't understand the solutions and therefore can't customize them to fit my needs.
我的问题已经得到了一遍又一遍的回答，但我不了解解决方案，因此无法自定义它们以满足我的需求。
Here's a link to a solution on StackOverflow Loop through each row of a range in Excel.
这是 StackOverflow Loop上的解决方案的链接，可以遍历 Excel 中某个范围的每一行。
Dim rng As Range
Dim row As Range
Dim cell As Range
Set rng = Range("A2:b22")
For Each row In rng.Rows
For Each cell in row.Cells
'Do Something
MsgBox cell
Next cell
Next row
Here's my code that should go inside the loop. It should take the value of the first column, copy the data, then take the valule of the 2nd column and paste the data.
这是我应该进入循环的代码。它应该取第一列的值，复制数据，然后取第二列的值并粘贴数据。
What it's doing is using the same value from the 1st column. So at what point does the value of CELL change to B2 from A2?
它正在做的是使用第一列中的相同值。那么CELL的值在什么时候从A2变为B2呢？
Windows("UnitedOrig.xlsx").Activate
Sheets(CurYearTxtPRAC).Select
Range("A4:U4").Select
ColumnFROM = MyColumnLetter(Cells.Find(What:=cell, After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column)
Range(ColumnFROM & "5:" & ColumnFROM & LastRowPRAC).Select
Selection.Copy
Windows("United.xlsx").Activate
Sheets("PRACS").Select
Range("A1:U1").Select
ColumnTO = MyColumnLetter(Cells.Find(What:=cell, After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column)
回答by Jon Crowell
In order to understand what the loop is doing, you need to monitor what is being processed. You can easily do that by changing the color of the cell or adding a border to the row.
为了了解循环正在做什么，您需要监视正在处理的内容。您可以通过更改单元格的颜色或向行添加边框来轻松完成此操作。
Paste both of these subs into a module:
将这两个子程序粘贴到一个模块中：
Sub WhereInDoubleLoop()
Dim rng As Range
Dim row As Range
Dim cell As Range
Set rng = Range("A1:D5")
For Each row In rng.Rows
' Do something to the row
row.BorderAround xlContinuous, xlThin, vbBlack
For Each cell In row.Cells
' Do something to the cell
cell.Interior.Color = vbYellow
Next cell
Next row
End Sub
Sub WhereInSingleLoop()
Dim rng As Range
Dim cell As Range
Set rng = Range("A1:D5")
For Each cell In rng
' Do something to the cell
cell.Interior.Color = vbGreen
Next cell
End Sub
Step through each of the loops, (F8 in the code editor), and you should gain a better understanding of exactly what is happening. Once you understand them, you can modify them for your own loop.
逐步执行每个循环（代码编辑器中的 F8），您应该对正在发生的事情有更好的了解。一旦你理解了它们，你就可以为你自己的循环修改它们。
回答by Kurt Wagner
I recently did something similar (I actually did comparison of multiple cells over two different sheets) and am new to VBA, but instead of ranges I based my loops off of specific row/column numbers and I found out you don't need .Activate for reading or writing or changing cells (the below method I used instead of .Select/.Activate made runtime roughly 300-400% faster). Here's some very quick psuedo-esque code that hopefully should make sense and should be easily modifiable to what you need.
我最近做了一些类似的事情（我实际上在两个不同的工作表上比较了多个单元格）并且我是 VBA 的新手，但是我基于特定的行/列号而不是范围我的循环，我发现你不需要 .Activate用于读取或写入或更改单元格（我使用以下方法而不是 .Select/.Activate 使运行时快了大约 300-400%）。这是一些非常快速的伪代码，希望它们应该有意义，并且应该可以轻松修改为您需要的。
Dim oSheet As Excel.Worksheet, vSheet As Excel.Worksheet
Dim oRowCount As Integer, vRowCount As Integer
Dim oRow, vRow
Dim someDataToCopy As String
'Gets last row # for oSheet & vSheet
oRowCount = oSheet.Cells.SpecialCells(xlLastCell).row
vRowCount = vSheet.Cells.SpecialCells(xlLastCell).row
For oRow = 2 to oRowCount
someDataToCopy = oSheet.Cells(oRow, 4).Value 'Where 4 is an arbitrary column
For vRow = 2 to vRowCount
vSheet.Cells(vRow, 8).Value = someDataToCopy 'Where 8 is an arbitrary column
Next vRow
Next oRow
You can add more 'temp' data objects (eg someDataToCopy2) to correspond if you're copying not just one cell per row, but multiple cells per row as well as adding more "= oSheet.Cells(oRow, x).Value" as needed. Let me know if you have any questions about what the code is doing!
如果您不只是复制每行一个单元格，而是每行复制多个单元格以及添加更多“= oSheet.Cells(oRow, x).Value”，则可以添加更多“临时”数据对象（例如 someDataToCopy2）以进行对应如所须。如果您对代码的作用有任何疑问，请告诉我！
