如何让 Excel VBA 在 If 和 Then 之后停止
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21283321/
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 to get Excel VBA to stop after a If and Then
提问by user3049027
I'm using the following code to check down a range of cells, and I would like it to stop and highlight the first empty one from the top.
我正在使用以下代码检查一系列单元格，我希望它停止并突出显示顶部的第一个空单元格。
Sub setcoinsfocus()
If Sheet29.Range("B3").value = "" Then Sheet29.Range("B3").Activate
If Sheet29.Range("B4").value = "" Then Sheet29.Range("B4").Activate
If Sheet29.Range("B5").value = "" Then Sheet29.Range("B5").Activate
If Sheet29.Range("B6").value = "" Then Sheet29.Range("B6").Activate
If Sheet29.Range("B7").value = "" Then Sheet29.Range("B7").Activate
If Sheet29.Range("B8").value = "" Then Sheet29.Range("B8").Activate
If Sheet29.Range("B9").value = "" Then Sheet29.Range("B9").Activate
If Sheet29.Range("B10").value = "" Then Sheet29.Range("B10").Activate
If Sheet29.Range("B11").value = "" Then Sheet29.Range("B11").Activate
If Sheet29.Range("B12").value = "" Then Sheet29.Range("B12").Activate
If Sheet29.Range("B13").value = "" Then Sheet29.Range("B13").Activate
If Sheet29.Range("B14").value = "" Then Sheet29.Range("B14").Activate
'Call MsgBox("You have entered too many coins. Maximum 12 coins per transaction. Please wait for assistance", vbCritical Or vbDefaultButton1, "Error")
'Call requestassist
'End If
End Sub
However, excel is actually highlighting the last empty one. I tried putting the code in the other order, but then it obviously highlights the first empty cell from the bottom. I tried putting in some End Ifs after each line, but it gives me an error 'End if without block if'. Any ideas on how to do this? Many thanks!
但是，excel实际上是突出显示最后一个空的。我尝试将代码按其他顺序排列，但它显然突出显示了底部的第一个空单元格。我尝试在每行之后放入一些 End Ifs，但它给了我一个错误“如果没有阻止，则结束”。关于如何做到这一点的任何想法？非常感谢！
回答by Dmitry Pavliv
You can use loop for it:
您可以使用循环：
Sub setcoinsfocus()
For i = 3 To 14
If Sheet29.Range("B" & i).Value = "" Then
Sheet29.Range("B" & i).Activate
Exit For
End If
Next
End Sub
or ElseIfstatement:
或ElseIf声明：
Sub setcoinsfocus()
If Sheet29.Range("B3").Value = "" Then
Sheet29.Range("B3").Activate
ElseIf Sheet29.Range("B4").Value = "" Then
Sheet29.Range("B4").Activate
ElseIf Sheet29.Range("B5").Value = "" Then
Sheet29.Range("B5").Activate
ElseIf Sheet29.Range("B6").Value = "" Then
Sheet29.Range("B6").Activate
ElseIf Sheet29.Range("B7").Value = "" Then
Sheet29.Range("B7").Activate
ElseIf Sheet29.Range("B8").Value = "" Then
Sheet29.Range("B8").Activate
ElseIf Sheet29.Range("B9").Value = "" Then
Sheet29.Range("B9").Activate
ElseIf Sheet29.Range("B10").Value = "" Then
Sheet29.Range("B10").Activate
ElseIf Sheet29.Range("B11").Value = "" Then
Sheet29.Range("B11").Activate
ElseIf Sheet29.Range("B12").Value = "" Then
Sheet29.Range("B12").Activate
ElseIf Sheet29.Range("B13").Value = "" Then
Sheet29.Range("B13").Activate
ElseIf Sheet29.Range("B14").Value = "" Then
Sheet29.Range("B14").Activate
End If
'Call MsgBox("You have entered too many coins. Maximum 12 coins per transaction. Please wait for assistance", vbCritical Or vbDefaultButton1, "Error")
'Call requestassist
'End If
End Sub
回答by ttaaoossuu
Try using Exit Substatement where you want to stop Sub.
尝试Exit Sub在要停止 Sub 的地方使用语句。
