Solved Exercise
Exercise Solution

We start by defining the function's arguments and the output data type
Function PrimeCount(Search As Range) As Long

We declare the variables
Dim i As Integer
Dim Cell As Range
Dim CellVal As Integer
Dim Divisor As Integer
Dim Prime As Integer

We create a For Each loop that runs through each cell of the function's "Range" argument. Then, we define the CellVal variable as the value of an element of the "Range" of the loop, and we assign 0 the Divisor variable
For Each Cell In Search
CellVal = Cell.Value
Divisor = 0

We create a For Next loop within the previous loop.
For i = 1 To CellVal

Within this loop we introduce a If Else conditional to calculate the number of divisors of the number. If CellVal Mod i = 0 Then Divisor = Divisor + 1

We use a If Else conditional to push the loop forward if the number of divisors reaches three (thus optimizing execution time).
If Divisor = 3 Then
Exit For
End If
End If
Next i

In another conditional, if the number has only two divisors, we add a unit to the counter Prime and move on to the next cell
If Divisor = 2 Then
Prime = Prime + 1
End If
Next Cell

We define the variable Prime as the total amount of prime numbers occurrences once the For Each loop ends
PrimeCount = Prime
Consolidated Answer
Function PrimeCount(Search As Range) As Integer
Dim i As Integer
Dim Cell As Range
Dim CellVal As Integer
Dim Divisor As Integer
Dim Prime As Integer
For Each Cell In Search
CellVal = Cell.Value
Divisor = 0
For i = 1 To CellVal
If CellVal Mod i = 0 Then
Divisor = Divisor + 1
If Divisor = 3 Then
Exit For
End If
End If
Next i
If Divisor = 2 Then
Prime = Prime + 1
End If
Next Cell
PrimeCount = Prime
End Function
Sub Verification()
Dim Search As Variant
Dim i As Integer
Dim Cell As Variant
Dim Divisor As Integer
Dim CellVal As Integer
Set Search = Range("A3:C103")
For Each Cell In Search
CellVal = Cell.Value
Divisor = 0
For i = 1 To CellVal
If CellVal Mod i = 0 Then
Divisor = Divisor + 1
If Divisor = 3 Then
Exit For
End If
End If
Next i
If Divisor = 2 Then
Cell.Interior.Color = RGB(140, 198, 63)
End If
Next Cell
End Sub
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2023 SuperExcelVBA  ABOUT