Hi Matt,

Paste this into a general module in the workbook you are dealing with.

Function Prime(TestRng As Range)

'code by Tom Ogilvy (modified from original by Myrna Larson)

'slight modification by RPS to accept range

Dim PrimeCnt As Long

Dim y As Long

Dim x As Long

Dim i As Integer

Dim Flag As Boolean

Dim Primes() As Long

Dim NumStop As Double

Dim TestNum As Long

ReDim Primes(1 To 2)

TestNum = TestRng.Value

NumStop = Sqr(TestNum)

If TestNum = 1 Or _

TestNum = 2 Or _

TestNum = 3 Or _

TestNum = 5 Then

Prime = True

Exit Function

End If

Primes(1) = 2

Primes(2) = 3

PrimeCnt = 2

x = 3

Do

x = x + 2

For y = 3 To Sqr(x) Step 2

If x Mod y = 0 Then GoTo NoPrime1

Next y

PrimeCnt = PrimeCnt + 1

ReDim Preserve Primes(1 To PrimeCnt)

Primes(PrimeCnt) = x

NoPrime1:

Loop Until Primes(PrimeCnt) > NumStop

For i = LBound(Primes) To UBound(Primes)

If TestNum Mod Primes(i) = 0 Then

Debug.Print i, Primes(i), TestNum / Primes(i)

Prime = False

Exit Function

End If

Next

Prime = True

End Function

Then to see if the value in A1 is prime, type "=prime(A1)" in your chosen cell.

HTH

## Like this thread? Share it with others