vba 对于excel宏中的每个循环vba跳跃交替行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14445743/
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
for each loop in excel macros vba jumping alternate rows
提问by antnewbee
I have the below code in vba. The rows in sheet5 from columns a5 to a20 are:
我在 vba 中有以下代码。sheet5 中从 a5 列到 a20 列的行是：
a5=Sweden
a5=瑞典
a6=Spain
a6=西班牙
a7=Russia
a7=俄罗斯
a8=Italy
a8=意大利
a9=Germany
a9=德国
a10=Finland
a10=芬兰
a11=Norway
a11=挪威
a12=Switzerland
a12=瑞士
a13=France
a13=法国
a14=Belgium
a14=比利时
Set fillcolumnrange = Sheet5.Range("A5:A20")
i = 1
For Each Row In fillcolumnrange.Rows
If Not Sheet5.Range("A" & i + 4) = "" Then
MsgBox Row(i)
End If
i = i + 1
Next Row
But this code is prompting only alternate values ie.
但是此代码仅提示备用值，即。
Sweden
瑞典
Russia
俄罗斯
Germany
德国
Norway
挪威
France
法国
Can anyone please help me out find the bug in the code
谁能帮我找出代码中的错误
回答by Rick
You were looping through the rows in your range and also advancing the variable iwithin your loop.
You can reference each variable that you are looping through.
您正在遍历范围内的行，并i在循环中推进变量。您可以引用正在循环的每个变量。
Try this
尝试这个
Set fillcolumnrange = Sheet1.Range("A5:A20")
For Each cell In fillcolumnrange.Cells
If Not cell = "" Then
MsgBox cell
End If
Next cell
回答by whytheq
You've got a mixture of different types of loop.
您已经混合了不同类型的循环。
Either do what Rick says.
要么按照瑞克说的去做。
Or use i:
或使用i：
Set fillcolumnrange = Sheet5.Range("A5:A20")
For i = 1 To fillcolumnrange.Rows.Count
If Not Sheet5.Range("A" & i + 4) = "" Then
MsgBox Sheet5.Cells(i + 4, 1)
End If
Next i
Or maybe a do-Loop
或者也许一个 do-Loop
Set fillcolumnrange = Sheet5.Range("A5:A20")
i = 1
do until i = fillcolumnrange.Rows.Count + 4
If Not Sheet5.Range("A" & i + 4) = "" Then
MsgBox Sheet5.Cells(i + 4, 1)
End If
i=i+1
Loop
(EDITnow tested and seem to run ok)
（EDIT现在经过测试，似乎运行正常）
回答by Peter Albert
Building on Rick's answer, here's the short version of the For Eachloop:
基于 Rick 的回答，这是For Each循环的简短版本：
For Each cell in fillcolumnrange.Cells
If len(cell) <> 0 Then MsgBox cell
Next
