In this post, we are going to look at using arrays and functions together. Our goal is to create a function that can divide the numbers in an array and count how many have a remainder.
Declare Function and Variables
First, we need to declare our function and variables. This is done in the code below.
Function remainderCount(rng as Range, n as integer) as Integer Dim i as Integer, j as Integer Dim nr As Integer, nc As Integer, c As Integer nr= rng.Rows.Count nc = rng.Columns.Count
The name of our function is remainderCount. It takes to arguments. Rng is for the array and n is for the divisor. In the next to lines, we declare variables for the following.
- i for row
- j for column
- nr for number of rows
- nc for number of columns
- c for the number of numbers that have a remainder
- The last to lines are for counting the number of rows and columns
Next, we need to create our nested for loop. This code has been used several times on this blog so we will not explain much here.
For i = 1 To nr For j = 1 to nc If rng.Cells(i,j) Mod n >= 1 Then c = c+1 End If Next J Next i remainderOne = c End Function
The for loop goes first across columns and then down to the next row. In each cell, it checks to see that if the number in that cell is divided by our n if there is a remainder or not. If so then the c variable increase by 1. After this, the output of the function is set to whatever value c is,
Below is the full code followed by what this looks like in Excel.
Function remainderCount(rng as Range, n as integer) as Integer Dim i as Integer, j as Integer Dim nr As Integer, nc As Integer, c As Integer nr= rng.Rows.Count nc = rng.Columns.Count For i = 1 To nr For j = 1 to nc If rng.Cells(i,j) Mod n = 1 Then c = c+1 End If Next J Next i remainderCount = c End Function
Below is the output
The answer is 4. This is because there are six numbers in the array. Two of the numbers are divisible by 4 (12 & 24) but the other four numbers are not (14, 18,19,27). The numbers that are not divisible by 4 make up the answer.
This is one of the more practical ways that you can employ knowledge of arrays with functions. Of course, there is an infinite number of ways to use these tools. This means that practice is the best way to learn how to apply these tools.