Excel VBA For Loop Not Working, Keeps coming up with error message

Question

Making a single player blackjack simulation but when I run it keeps coming back with the message:

Compile Error

Next without For

    For i = 1 To 5
    
        If win = "False" Then
            Range("C13").Select
            Selection.Insert Shift:=xlDown
            Range("B11").Select
            Selection.Copy
            Range("C13").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
            With Selection.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            
            Range("C18").Select
            Application.CutCopyMode = False
            ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
            Range("C19").Select
            ActiveCell.FormulaR1C1 = ""
            Range("H19").Select
    
            If Range("C18") < 22 And Range("C18") >= Range("B18") Then
                win = "True"
            End If
        
            If Range("C18") < 22 Then
                win = "Bust"
        End If
    Next i
    
    If win = "True" Then
        Application.Run "Dealer_wins"
    Else
        Application.Run "player_wins"
    End If
End Sub

count your Ifs and End Ifs. You are opening 3 If statements in the For Loop but only closing 2 of them. — Vulthil
– Vulthil, Commented Dec 7, 2015 at 12:46
Range("C18") >= Range("B18") this is condition will return true anyway, what does it mean in your code? — Vasily
– Vasily, Commented Dec 7, 2015 at 13:28

R3uK · Accepted Answer · 2015-12-07 12:49:21Z

3

You were simply missing an End If for the test on C18!

And I got rid of the useless .Select as this is a really resources consuming command that is most of the time absolutely not necessary!

    For i = 1 To 5

        If win = "False" Then
            Range("C13").Insert Shift:=xlDown
            Range("B11").Copy

            With Range("C13")
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                With .Interior
                    .Pattern = xlNone
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            End With

            Application.CutCopyMode = False
            Range("C18").FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
            Range("C19").FormulaR1C1 = ""

            If Range("C18") < 22 And Range("C18") >= Range("B18") Then
                win = "True"
            End If

            If Range("C18") < 22 Then
                win = "Bust"
            End If
        End If
    Next i

    If win = "True" Then
        Application.Run "Dealer_wins"
    Else
        Application.Run "player_wins"
    End If
End Sub

answered Dec 7, 2015 at 12:49

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gazzz0x2z Over a year ago

deleted my answer as it was a (not as good) double from yours. Just to add : the OP had an excellent indentation that allowed for quick bug identification. He just has to learn how to use this good habit.

R3uK Over a year ago

@gazzz0x2z : You should have let it to still get some upvotes, it is not an issue to have duplicate answers! It might confuse the asker a bit, but it is his job to test what we can propose! ;) Undelete it so I can upvote your answer and your fair play! ;)

Vasily · Accepted Answer · 2015-12-07 14:38:45Z

additional variant to already provided:

....

For i = 1 To 5
    If win = "False" Then
        With [C13]
            .Insert
            .Value = [B11].Value2
            With .Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End With
        [C18].Value = Application.Sum([C13:C17])
        [C19] = ""
        If [C18].Value2 < 22 And [C18].Value2 >= [B18].Value2 Then 'this condition shall be reviewed
            win = "True"
        ElseIf [C18].Value2 < 22 Then
            win = "Bust"
        End If
    End If
Next i
If win = "True" Then
    Application.Run "Dealer_wins"
Else
    Application.Run "player_wins"
End If

....

also, please pay attention to this line of your code:

Range("C18") >= Range("B18")

it always will be true, so this condition can be removed due to excessive part of code, or updated to compare [B18] with another cell.

gazzz0x2z · Accepted Answer · 2015-12-07 14:50:40Z

-1

That"s why there should never be more than 10-15 lines between the beginning & the end of the element. Here, the For-Next.

As your indentation is good(kudos for that), other commenters already spotted the mistake at the end :

        If Range("C18") < 22 Then
            win = "Bust"
    End If
Next i

That should be

        If Range("C18") < 22 Then
            win = "Bust"
        End If
    End If
Next i

NB : undeleted on R3uK's demand

edited Dec 7, 2015 at 14:50

answered Dec 7, 2015 at 12:50

gazzz0x2z

3262 silver badges12 bronze badges

Collectives™ on Stack Overflow

Excel VBA For Loop Not Working, Keeps coming up with error message

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related