0

How do I exit the For ITERATION in loop if the condition is met and then go to the next one. The sheet has several thousand lines and such a break in the loop would speed up the code. This is not exit from whole loop only skip one iteration.

For i = 1 To LastRow2
    For j = 1 To LastRow3
        If ark2.Cells(j, "F") = ark3.Cells(i, "J") Then
            GoTo NextIteration
        Else
            LastRow2 = ark2.Cells(Rows.Count, 1).End(xlUp).Row
            ark2.Rows(LastRow2 + 1) = ark3.Rows(i)
        End If

    Next j
Next i
gajus21
  • 3
  • 3
  • 2
    What are you actually trying to do? Two loops smells fishy. – BigBen Mar 12 '20 at 13:01
  • There is no `continue` statements in VBA. You would have to do something like this `If ark2.Cells(j, "F") <> ark3.Cells(i, "J") Then Your Code` – MGP Mar 12 '20 at 13:04
  • 1
    "exit for" will exit the innermost loop... if you want then entire loop exited in the inner loop, you can set a boolean outside the loop and continue only if the boolean is true (in other words, exit for on the outer loop if the bool has been turned off) – Hambone Mar 12 '20 at 13:06
  • Yes but if I have to every time check 100% lists it takes ~20min. If code finds a matching cell then it doesn't need to check it anymore but goes to the next iteration. The code will only be executed if it does not find any matching entries. – gajus21 Mar 12 '20 at 13:07
  • 1
    Does this answer your question? [Excel VBA - exit for loop](https://stackoverflow.com/questions/9414969/excel-vba-exit-for-loop) – D Stanley Mar 12 '20 at 13:08
  • 3
    Use a completely different approach.. Use arrays ;) – Siddharth Rout Mar 12 '20 at 13:09
  • If you want to find matches, maybe look [here](https://learn.microsoft.com/de-de/office/vba/api/excel.range.find) – MGP Mar 12 '20 at 13:10
  • D Stanley - sorry but no, that's why I'm asking. – gajus21 Mar 12 '20 at 13:11
  • Can someone stop changing question? It is not me question right now. – gajus21 Mar 12 '20 at 13:15
  • Welcome to SO. Your question is unclear because you don't specify what are you trying to do, and also, you don't specify what have you tried to speed up. Check https://stackoverflow.com/a/49514930/9199828 to speed up code and see if something there may help. – Foxfire And Burns And Burns Mar 12 '20 at 13:15
  • OK simpler: `If True then Next i Else CODE End If` – gajus21 Mar 12 '20 at 13:17
  • 1
    How is `Exit For` not the right answer here? You want to skip iterating any more `j` and go to the next `i`? Then `Exit For`... The only other interpretation I can muster from this question is that you want to skip to the next `j`? If that's the case then just do `If Not ark2.Cells(j, "F") = ark3.Cells(i, "J") Then... ` and put your code in there as @MG92 already suggested. Then your more expensive code will only execute if there isn't already a match. If there is it will iterate to the next `j`. – JNevill Mar 12 '20 at 13:19
  • Correct me if I am wrong. What you are trying to do is to check if an item from Col F exists in Col J and if it doesn't then write at the end of col J? – Siddharth Rout Mar 12 '20 at 14:22
  • Replace `GoTo NextIteration` with `Exit for`. However, this will still last forever, so you should have a look at using arrays (e.g. here https://www.microsoft.com/en-us/microsoft-365/blog/2009/03/12/excel-vba-performance-coding-best-practices/ ) – fourthquartermagic Mar 13 '20 at 13:59

0 Answers0