Excel In Your Office

Hiiii!

We have been discussing about looping in VBA and saw about For Nextlooping in our earlier post. Today, we will see about one more type of For Next loop.

For Each Loop

Our earlier method will work fine when we know exactly how many numbers of times the loop should work. But there might be some cases where your macro should run depending upon the situation. For example: what if your macro should run based on the number of sheets in the workbook? There might be 2/3/4….n sheets in that workbook where macro is being run by user.

In these cases, you can use For Each loop. Let’s see how to code this by an example. I need to write a macro to check that;

any cell in a range that user selects contains a formula
If at least one cell is found with formula, a message box should be displayed.

Since how many cells can be selected is in the hands of the user, we can’t hard code the looping number in the code. Let’s see how we can use For Each in this example.

Sub CheckFormula()
Dim Cl As Range 'Declaring a variable for cell
Dim HasF As Boolean 'Declaring a variable to flag if cell has formula
For Each Cl In Selection 'Declaring loop for each cell in selected range
    If Cl.HasFormula = True Then 'Check if that cell contains formula
        HasF = True 'formula is there, so HasF should be true
    End If
Next Cl
If HasF = True Then MsgBox ("There is atleast one cell with formula")
End Sub

I am using 2 variables here. Cl holds a range and HasF is a Boolean. Our code will loop through all the cells in a Selection (Selection is an object that is collection of all the cells selected). When you start executing the macro, each cell in the selection will be checked for Cl.HasFormula statement. This will return either True (if formula is there) or False (no formula). HasF will get its value from this result. At the end, we are checking the HasF final status; if it is true (that means formula is found somewhere) then a message box is displayed. Otherwise macro ends.

There is one more example of For Each available in the post: Unhide All Hidden Sheets using For Next & For Each. You may also continue reading about For Each Looping - For Next (3 of 3)