VBA Looping - For Next (3 of 3)

Hi, we saw how to use For Next to loop through any number of times and perform some tasks you code. In the last post that we saw, we wrote a code to check if any cell in the selection has formula using For Each statement. There is a slight efficiency problem in that code. When a particular loop identifies a cell that contains formula, there is no more need to continue looping, right? Since all that you want is to check if any one cell contains formula or not. Why to continue looping after you find one such cell? To avoid looping after a particular situation is encountered, you can use Exit For statement that stops looping. Let’s see how, using the very same example from previous post. Check out the code below.

Sub CheckFormula()                                                                                             
Dim Cl As Range 'Declaring a variable for cell
 For Each Cl In Selection 'Declaring loop for each cell in selected range
    If Cl.HasFormula = True Then 'Check if that cell contains formula
        MsgBox ("There is atleast one cell with formula")
        Exit For
    End If
Next Cl
 End Sub

Compare this code with our code from the previous post. I am using only one variable compared to two earlier. When my Cl.HasFormula statement returns True (that means formula is there), a message box is displayed and Exit For statement is executed. Loop will end at this point of time. This saves you on the precious execution time.

You may also look at: Unhide All Hidden Sheets using For Next & For Each