CODEWITHSUNDEEP
vbaexcel
user3049027
user3049027

Reputation: 195

How to get Excel VBA to stop after a If and Then

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!

Upvotes: 4

Views: 12140

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

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 ElseIf statement:

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

Upvotes: 4

ttaaoossuu
ttaaoossuu

Reputation: 7884

Try using Exit Sub statement where you want to stop Sub.

Upvotes: 2

Related Questions