How to exit for loop using VBA in Excel
By
SpreadCheaters
Under normal circumstances, the for loop keeps on working for a defined number of times and exits only when the pre-defined number of iterations are completed. However, sometimes, the application of For Loop demands premature exit from the loop. In that case, the “Exit For” command in Excel VBA is used to exit a For Loop, before the loop has completed all its iterations. This can be useful in situations where you want to stop the loop if a certain condition is met.
In this tutorial we’ll work on a similar practical example of when we might use the Exit For command inside a For Loop. Look at the following dataset above where we have a large range of names and we want to search for a specific employee name.
In Microsoft Excel, the “For Loop” is a type of loop structure in Visual Basic for Applications (VBA) that allows the user to repeat a set of statements a specific number of times. It is used to perform a repetitive task for a defined number of times. It is used in various applications to automate repetitive tasks in Excel. Here are a few examples of its usage:
Loop through a range of cells: You can use a For Loop to iterate through a range of cells and perform specific operations on each cell. For example, you can loop through a range of cells and sum the values in each cell.
Loop through an array: You can use a For Loop to iterate through an array and perform specific operations on each element. For example, you can loop through an array of names and extract the first letters of each name.
Step 1 – Write the VBA code in VBA Editor
– Open the VBA editor by pressing ALT+F11.
– Write the following code in the editor window.
– In the above code the For Loop goes through each cell in the range and checks that if the value in the cell B2 matches the name we’re looking for. If a match is found, we will use the Exit For command to stop the loop and display a useful message to the user in cell C2 about the location of the match found.
Step 2 – Run the VBA code in VBA Editor find Name
– We can run the code from the play button inside the VBA Editor or we can use the F5 shortcut key to run the code.
– The code searches for the name specified in cell B2 and exits using the Exit For command when a match is found. The benefit of using this command is that we don’t have to wait till the For Loop finishes off going through all the range. It will automatically exit whenever a match is found even if it is found in the first row, as shown above.
