Loop through cells inside the used range
(For Each Collection Loop)
In this video we count the number of cells that contain formulas on our worksheet.
We do this with Excel VBA with the help of the For Each Collection loop.
This allows us to loop through each cell inside the used range of the worksheet and, with the help of the IF statement, check whether we come across a formula cell and retain a count of the number of formulas detected.
The detection of a formula in a cell is accomplished using the HasFormula property of the range object.
We will use a variable to store the “detected formulas” number in memory and display the number in an Excel worksheet for the user.
The following is a summary of the steps we need to accomplish to perform this task:
- Write a macro that will loop through each cell in the used range of a sheet. We don’t want to loop through all 17 billion cells of the sheet; only the used cells.
- Set up a counter to keep track of the number of cells containing formulas. As we examine each cell in the used range, if the cell contains a formula we will add one to our counter.
- Once the examination is complete, display the result stored in the counter in cell B6 of the current worksheet.
- Create a macro button and assign the macro to the button. When the user clicks the button, the macro will execute and the value in cell B6 will be updated.
Writing the Macro
Open the Visual Basic Editor (Alt-F11 or Developer (tab) -> Code (group) -> Visual Basic).
Create a new module sheet called “ProjectActivity” by right-clicking on any item in the current workbook list of the Project Explorer and selecting Insert -> Module.
In the Properties windows, change the name of the new module sheet from Module1 to ProjectActivity.
In the code window we will create a new subprocedure named Count_If_Formula.
To loop through each cell, we will utilize a collection in conjunction with an IF…Then construct. The collection is the used range, and we need to declare a variable to hold a tiny part of the collection during the examination process. In this case, each tiny part is a single cell.
We need to establish a variable to hold the single cell during examination.
Now we will create the loop instruction to loop through each cell in the used range. Because “UsedRange” is a property of a worksheet, we can refer to the active sheet’s used range. We will go ahead and close the logic of the loop with the “Next cell” instruction.
Testing for the Presence of a Formula
Inside of the For…Next structure, let’s create a test to determine if the currently examined cell contains a formula. We will use an IF statement to compare the cell’s contents to the range property HasFormula.
The HasFormula property returns a “True” if the cell contains a formula.
Don’t forget to close the logic of the If…Then with an End If statement.
If the test of the cell returns a “True”, we need to keep record of that occurrence. We need to create a variable to store that occurrence. Then next time we encounter a formula, we will increment that counter by 1.
Create a new variable called “CountFormula” as a Long data type to store this value. The Long data type can hold a value in excess of 9 quadrillion, well in excess of the maximum 17 billion cells on a single sheet.
Accounting for the Presence of a Formula
If the examined cell possesses a formula, we will add 1 to the counter named “CountFormula”. This is performed by the following line of code.
When the macro begins, the value of the variable “CountFormula” is automatically set to 0 (zero). As we loop though each cell in the used range testing for the presence of a formula, if the cell contains a formula, we add 1 (one) to the variable. This overwrites the current value with the updated value.
Adding a Macro Launch Button
Returning to the Excel workbook, we will add a macro launch button and assign the newly created macro to the button.
From the ribbon, select Developer (tab) -> Controls (group) -> Insert -> Form Controls -> Button (upper left option).
Click anywhere on the active sheet.
From the Assign Macro dialog box, select the Count_If_Formula macro from the list of available macros and select OK.
This will create the macro launch button on the sheet.
Rename the button to “Count Formula” by right-clicking on the button and selecting Edit Text.
You may need to resize the button to display the fully renamed text.
Testing the Macro
Click the Count Formula button and observe the value in cell B6. The current value is 2, since we have formulas in cells B8 and B9.
Add a formula to cell B10 (any formula will do) and execute the macro. The value of cell B6 has been updated to 3.
NOTE: In this case, since we are putting the button on the same sheet we are examining we don’t need to reference the sheet name. We can use “ActiveSheet” and directly use range and not have to specify the sheet. The assumption is that the sheet being examined is the active sheet. If you were going to put your button on a different sheet and you wanted it to count the formulas on this specific sheet, you need to reference the worksheet names.
The Completed Code
Sub Count_IF_Formula() Dim cell As Range Dim CountFormula As Long For Each cell In ActiveSheet.UsedRange If cell.HasFormula Then CountFormula = CountFormula + 1 End If Next cell Range("B6").Value = CountFormula End Sub
Unlock Excel VBA & Macros Course is here.
Save time. Achieve more.
Over 50 Excel macro examples for download & useful VBA codes you can use for your work.
Learn the WHY not just the HOW