如何让 excel vba 移动到 for 循环中的下一个单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9301419/
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
how do i make excel vba move to the next cell in a for loop?
提问by franklin
I have here the code for a Macro that does some data pick and place from one worksheet to another. Right now this code does 90% of what I want it to do. The problem is that if it encounters a source cell that has nothing in it (Hence Len(cellVal) = 0and cellVal = ""it overwrites the destination cell.
我在这里有一个宏的代码，它执行一些数据从一个工作表到另一个工作表的拾取和放置。现在这段代码完成了我想要它做的 90% 的事情。问题是，如果遇到有什么也没有（因此源小区Len(cellVal) = 0和cellVal = ""它覆盖目标单元格。
You will see that at the second ElseIf block there is a comment. Right now it does nothign but if the statement evaluates to true, i.e. there is nothing in the source cell, I want Excel to move on to the next source cell WITHOUT modifying the destination cell's contents.
您将看到在第二个 ElseIf 块中有一条注释。现在它什么都没有，但是如果语句的计算结果为真，即源单元格中没有任何内容，我希望 Excel 移动到下一个源单元格而不修改目标单元格的内容。
Any ideas on how this might be accomplished?
关于如何实现这一点的任何想法？
Franklin
富兰克林
For i = 7 To endPointFlash
Dim cellVal As String
cellVal = Cells(i, "G")
If (Len(cellVal)) > 0 Then
RawData.Activate
ElseIf (Len(cellVal)) = 0 Or cellVal = "" Then
' need to tell excel to do nothing and move to the next cell
End If
For j = 1 To endPointRaw
If cellVal = Mid(Cells(j, "A"), 1, Len(cellVal)) Then
val2 = Mid(Cells(j, "A"), 1, Len(cellVal))
val3 = Cells(j, "D")
Flash.Cells(i, "H").Value = val3
Exit For
Else: Flash.Cells(i, "H").Value = 0
End If
Next j
Flash.Activate
Next i
回答by Reafidy
Like this:
像这样：
For i = 7 To endPointFlash
Dim cellVal As String
cellVal = Cells(i, "G")
If (Len(cellVal)) > 0 Then
RawData.Activate
ElseIf (Len(cellVal)) = 0 Or cellVal = "" Then
' need to tell excel to do nothing and move to the next cell
Else
For j = 1 To endPointRaw
If cellVal = Mid(Cells(j, "A"), 1, Len(cellVal)) Then
val2 = Mid(Cells(j, "A"), 1, Len(cellVal))
val3 = Cells(j, "D")
Flash.Cells(i, "H").Value = val3
Exit For
Else: Flash.Cells(i, "H").Value = 0
End If
Next j
End If
Flash.Activate
Next i
Or like this:
或者像这样：
For i = 7 To endPointFlash
Dim cellVal As String
cellVal = Cells(i, "G")
If (Len(cellVal)) > 0 Then
RawData.Activate
ElseIf (Len(cellVal)) = 0 Or cellVal = "" Then
' need to tell excel to do nothing and move to the next cell
Goto NextLoop
End If
For j = 1 To endPointRaw
If cellVal = Mid(Cells(j, "A"), 1, Len(cellVal)) Then
val2 = Mid(Cells(j, "A"), 1, Len(cellVal))
val3 = Cells(j, "D")
Flash.Cells(i, "H").Value = val3
Exit For
Else: Flash.Cells(i, "H").Value = 0
End If
Next j
NextLoop:
Flash.Activate
Next i
回答by Jean-Fran?ois Corbett
To be very clear and explicit I would write this:
为了非常清楚和明确，我会这样写：
Dim cellVal As String
For i = 7 To endPointFlash
cellVal = Flash.Cells(i, "G")
If Len(cellVal) = 0 Then
' Do nothing.
Else
For j = 1 To endPointRaw
If cellVal = Mid(RawData.Cells(j, "A"), 1, Len(cellVal)) Then
val2 = Mid(RawData.Cells(j, "A"), 1, Len(cellVal))
val3 = RawData.Cells(j, "D")
Flash.Cells(i, "H").Value = val3
Exit For
Else
Flash.Cells(i, "H").Value = 0
End If
Next j
End If
Next i
Here it's pretty clear to the reader that a cell containing an empty string should result in no action; in all other cases do the following.
在这里读者很清楚，包含空字符串的单元格应该不会导致任何操作；在所有其他情况下，请执行以下操作。
Also I don't activate each sheet back and forth. Doing this makes your code go slower (and makes your screen flicker). Instead I identify each call to Cellsusing the proper sheet name. Again this makes it very explicit where you're getting things.
此外，我不会来回激活每张纸。这样做会使您的代码运行速度变慢（并使您的屏幕闪烁）。相反，我Cells使用正确的工作表名称识别每个调用。这再次使您非常明确地了解您从哪里得到东西。
NB: cellVal = ""necessarily implies Len(cellVal)) = 0, so there's no need to write both in your conditional.
注意：cellVal = ""一定意味着Len(cellVal)) = 0，所以没有必要在你的条件中同时写。
回答by assylias
You could try this (not sure if your Flash.Activateshould be executed when the cell is empty - if so move it below the End If):
你可以试试这个（不确定你Flash.Activate是否应该在单元格为空时执行 - 如果是这样，将它移到 下方End If）：
Dim cellVal As String
For i = 7 To endPointFlash
cellVal = Cells(i, "G")
If cellVal <> "" Then
RawData.Activate
For j = 1 To endPointRaw
If cellVal = Mid(Cells(j, "A"), 1, Len(cellVal)) Then
val2 = Mid(Cells(j, "A"), 1, Len(cellVal))
val3 = Cells(j, "D")
Flash.Cells(i, "H").Value = val3
Exit For
Else
Flash.Cells(i, "H").Value = 0
End If
Next j
Flash.Activate
End If 'Else cell is empty: do nothing
Next i
