vba - 如何让 Excel VBA 在 If and Then 之后停止

Question

我正在使用以下代码检查一系列单元格，我希望它停止并突出显示顶部的第一个空单元格。

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

但是excel其实是高亮最后一个空的。我尝试以其他顺序放置代码，但它显然突出显示了底部的第一个空单元格。我尝试在每一行之后放入一些 End If，但它给了我一个错误“End if without block if”。关于如何做到这一点的任何想法？非常感谢!

Answer 1

你可以为它使用循环:

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

或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