Continue For doesn't work

Question

I'm trying to use the Continue For statement in Excel and it throws a compilation error: "Expected expression". This is the code:

For i = 3 To num
If Cells(i, 19) = "" Then
continue for
End If
Next i

Why does this happen? PS: this a simplified version of the actual code.

continue for does not exist in vba, because in most cases there is a way to rearrange the statement in the loop to make it unneeded. If you show the actual code we may be able to help re work the logic. — Scott Craner, Jan 25 '16 at 18:00
Use `Option Explicit` and you'll see that `Continue` isn't a keyword in VBA, you can use a `GoTo` instead. — Kyle, Jan 25 '16 at 18:01
I want it to go to the next loop iteration. That is, I need it to add +1 to i and start over. — N. Pavon, Jan 25 '16 at 18:02
If the condition is met then do nothing and the loop will move to the next loop without doing anything. It is a matter of using the correct if statements. In this instance you would do an if statement of `If Cells(i, 19) <> "" Then` and do those things. that way if it is blank it will do nothing and the next loop will fire. — Scott Craner, Jan 25 '16 at 18:06
For the record I have rolled back the question to the state prior to the last edit, so that it is clear what the original question was - the question should not be edited to reflect incorporation of the chosen answer, as that makes it difficult for later users to actually see what the initial problem was. — Grade 'Eh' Bacon, Jan 25 '16 at 18:09
Possible duplicate of [VBA - how to conditionally skip a for loop iteration](http://stackoverflow.com/questions/8680640/vba-how-to-conditionally-skip-a-for-loop-iteration) — BrakNicku, Jan 25 '16 at 18:12

score 7 · Accepted Answer · answered Jan 25 '16 at 18:06

7

The simplest would be to simply process when the condition is not met.

For i = 3 To num
    If Cells(i, 19) <> "" Then
        'execute the rest of the processing
    End If
Next i

Avoid incrementing the i in a For Next loop. A GoTo is discouraged and largely unnecessary (as demonstrated above).

Alternates would be For Each in a Range.SpecialCells method of xlCellTypeConstants if the values are typed values and not returned from a formula.

answered Jan 25 '16 at 18:06

Are there any major practical differences in using `Cells() <> ""` over `IsEmpty(Cells())`? – BruceWayne Jan 25 '16 at 18:14
2

`Cells(...) <> ""` is kind of a catch-all that matches truly blank cells as well as cells containing zero-length string results from formulas. `Not IsEmpty(Cells(...))` catches only truly blank cells. I also use `If CBool(Len(Cells(...))) Then` . – Jan 25 '16 at 18:16
Thanks! I had it backwards - I thought `<> ""` would catch almost everything, but `IsEmpty()` would catch every type of empty cell. I like your last one, but why use `Cbool`? Wouldn't `If Len(Cells())` on an empty cell return 0, making it `FALSE`? does CBool force that into a boolean, whereas if you didn't use it, it'd return the string `0`, and thus not `FALSE` as VBA reads it? (...did that question make sense?) – BruceWayne Jan 25 '16 at 18:26
2

`CBool` is a definitive conversion from a number to a boolean. While it can be said that False is zero and anything that is not False is True, in reality, VBA doesn't always treat a non-zero number as True without CBool. Best to be definitive and it's a low-impact visual reminder that you are evaluating a boolean. – Jan 25 '16 at 18:29

Karl Hoaglund · Answer 2 · 2023-08-12T16:46:56.200

I had the same problem as the OP. It looks like VBA does not have a "continue for" command. The .Net For statement does, so it is easy to land on documentation like this and think that VBA has it as well.

My solution was to use a goto statement. I know, I know ... Gotos are disfavored. But if used carefully they can really clean up your code.

    For intRow = 1 To intLastRow
        strAddr = "a" & intRow
        If wsDest.Range(strAddr) = "X" Then
            GoTo next_intRow
        End If
        
        ... statements
        
next_intRow:
    Next intRow

And I respectfully disagree with the first answer. While I rarely use continue statements, they are occasionally very helpful. Without them you have to use the NOT of your elimination logic and wrap potentially large blocks of code in if-endif statements, which I think makes the code much messier and harder to read.

Continue For doesn't work

2 Answers2