Excel VBA:

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:

  1. 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.
  2. 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.
  3. Once the examination is complete, display the result stored in the counter in cell B6 of the current worksheet.
  4. 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.

Displaying the Final Value to the User

We want to show the user the result of the cell examinations.  We want to display the value stored in the “CountFormula” variable in cell B3 of the current sheet.  The following line of code will perform this action.

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

LEARN MORE

2 Comments

  1. Jeyner Lopez May 5, 2019 at 3:42 am - Reply

    Hi, Great job. I would like to know how can I count how many time a number followed another number, I have a column with a random sequence fro 1 to 48 the column is dynamic, I am looking for how many time for example 2 is after 1, 3 after one and so on and start the counting from the button to the top, the size of the column now in A2700.

    • Bryon Smedley May 5, 2019 at 11:15 am - Reply

      Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

      The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

      Microsoft Excel Tech Community

      With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

      Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

      The XelPlus Team

Leave A Comment

Share This