CODEWITHSUNDEEP
vbaexcel
user2600411
user2600411

Reputation: 71

exit for and keep looping the upper level for

I want to keep looping the outside for after exiting the inside for in the if statement, the logic should be right but I don't know how to code it. I did this and it gives me an "next without for" error. Ideas?

Here is my code:

For InrCounter = 2 To InrNum
    For ExrCounter = 2 To Exrnum

            'add missing column data from input sheet to existing sheet
            lastCofthisR = sheet1Table.Cells(ExrCounter, Columns.Count).End(xlToLeft).Column

            'searching for address
            If sheet1Table.Cells(InrCounter, 1) = sheet2Table.Cells(ExrCounter, 1) And sheet1Table.Cells(InrCounter, 2) = sheet2Table.Cells(ExrCounter, 2) And sheet1Table.Cells(InrCounter, 3) = sheet2Table.Cells(ExrCounter, 3) Then
                If lastCofthisR < IncNum Then
                    For LastCofRowCounter = lastCofthisR + 1 To IncNum
                        Sheets("Sheet1").Cells(ExrCounter, LastCofRowCounter) = Sheets("Sheet2").Cells(InrCounter, LastCofRowCounter)
                    Next LastCofRowCounter

                    'found match loop next input row
                    Exit For
                    Next InrCounter
                Else
                    'do nothing
                End If
            Else
                Next ExrCounter
                'do nothing
            End If


            'did not find the input row, find the last row of existing and add it
            lrowEx = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            For counterCofLastR = 1 To IncNum
                Sheets("Sheet1").Cells(lrowEx + 1, counterCofLastR) = Sheets("Sheet2").UsedRange.Columns(1).Cells(InrCounter, counterCofLastR)
            Next counterCofLastR
     Next InrCounter

Upvotes: 0

Views: 92

Answers (1)

Ripster
Ripster

Reputation: 3595

You cannot start a For loop and then have the Next statement embedded in an If statement.

Example:

For i = 0 To 10
    If Range("A1").Value = "" Then
    Next
End If

Will throw an error because Next is inside the if statement. The proper way to do this would be:

For i = 0 To 10
    If Range("A1").Value = "" Then
    End If
Next

It also looks like you have one too many Next statements.

Edit:

To jump out of a loop you can use Exit For

For example:

For i = 0 To 10
    For x = 0 To 10
        Exit For
        Debug.Print x
    Next
    Debug.Print i
Next

only i will get printed 0 to 10 since the inner loop will hit Exit For

If you want to skip a loop iteration you can manually increment the loop counter. For example the following code will skip 5 because i is immediately increased at the beginning of the loop.

For i = 0 To 10
    If i = 5 Then
        i = i + 1
    End If
    Debug.Print i
Next

Upvotes: 2

Related Questions