Conditionally exit from nested for loop - VBA

Question

I have the following issue. I have several source file to copy from into my summary sheet (the order of columns/rows can differ). Thus, I am trying to do some hard-coded check with a h/v-lookup combo. The code is the following:

For i = 2 To Row_number

    '''' 1150 ''''
    If (Cells(i, 42).Value = "1150") Then

        If (WB_1150 Is Nothing) Then
            Set WB_1150 = Workbooks.Open(directory & file_1150)

            For Each wb In Workbooks
                If (wb <> ThisWorkbook & wb <> WB_1150) Then
                    wb.Close savechanges = False
                End If
            Next wb
        End If

        bool_vlookup_result = IsError(Application.vlookup(ThisWorkbook.Sheets(sheet_name_swaption).Cells(i, 12), WB_1150.Sheets(1).range("V1:V" & Row_number), 1, False))

        If (bool_vlookup_result = True) Then
            ThisWorkbook.Activate
            Sheets(sheet_name_swaption).Activate
            Cells(i, 43).Value = "ERROR"
            Next i
        Else
            row_index_result = Application.Match(Cells(i, 2), WB_1150.Sheets(1).range("V1:V" & Row_number), 0)

            For j = 1 To 42

                If (Cells(row_index_result, j) = "") Then
                    Next j
                Else
                    bool_hlookup_result = IsError(Application.HLookup(ThisWorkbook.Sheets(sheet_name_swaption).Cells(i, j), WB_1150.Sheets(1).range(Cells(row_index_result, 1), Cells(row_index_result, 22)), 1, False))

                    If (bool_hlookup_result = True) Then
                        ThisWorkbook.Activate
                        Sheets(sheet_name_swaption).Activate
                        Cells(i, 43).Value = "ERROR"
                        Next i
                    End If

                End If

            Next j

            ThisWorkbook.Activate
            Sheets(sheet_name_swaption).Activate
            Cells(i, 43).Value = "OK"
        End If

    End If
    '''' End 1150 ''''

''' OTHER SOURCE FILES '''

Next i

I get the error Next without For, because as soon as I got an error I can skip to the following i/j. Any suggestions to solve/improve this? I know that there can be several ways to do those checks, but this is the most powerful (and most time-consuming though) instrument that I found. Many thanks in advance.

Kapol · Accepted Answer · 2014-03-11 09:58:36Z

1

You can do it in such way:

For i = 1 To 10
    If i = 3 Then GoTo Cont
    Debug.Print i
Cont:
Next i

answered Mar 11, 2014 at 9:58

Kapol

6,4834 gold badges26 silver badges47 bronze badges

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

5 Comments

7raiden7 Over a year ago

How can I do it with two nested for loops? Anyway, is there a way to avoid spaghetti-programming (i.e. GoTo)? I was looking for the C-analogue to continue/break.

Kapol Over a year ago

Maybe you could create wrapper functions, whose calls will be dependant on a conditional statement. I'm not sure if it's possible in your example, because I didn't look into it in details.

7raiden7 Over a year ago

Do you mean a wrapper function that contains the correct GoTo? It could do, but it doesn't look as natural as I thought. I will give it a try and I'll let you know. Thanks.

Kapol Over a year ago

I know the general attitude is to avoid GoTo whenever possible, but in VBA it is often the best solution.

7raiden7 Over a year ago

I restructured the code, taking the opposite condition between the if, so that I used only one GoTo, and now it is feasible, and probably working. Thanks again.

Collectives™ on Stack Overflow

Conditionally exit from nested for loop - VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related