"1" messing up prime no. module

**173** · 07-13-2008, 12:18 AM

I don't know how to write modules yet, so I downloaded the one below. It checks to see if a number is a prime. It's cool and works, except any time that a 1 comes up. When I referrence a cell (B2 for example)that's value =1, with =isprime(B2), then I recieve "# value" as a result. This messes up the rest of my spreadsheet.

Please Login or Register  to view this content.

Can someone pleace help me with this?

**se1429** · 07-13-2008, 01:14 AM

Hi,

You are getting "#value" error because "1" is not a prime number.
If you want to consider it as prime for any reason then change the following line in your code:

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Regards

**173** · 07-13-2008, 02:46 AM

Thanks for the responce.
This module gives an answer as if its true or false.
On all the other non prime numbers (other than one), it gives a false result, but not "# value" (as with the number one).
On primes, it gives a true result.
I need it to just treat ones like all other non primes.

**mudraker** · 07-13-2008, 03:07 AM

173

Please take a couple of minutes and read the Forum Rules - Rule 3

As there is a 2hour time limit forediting threads I have wrapped your VBA code.

Moderators can close/delete threads or ban users who continue to breach forum rules

**173** · 07-13-2008, 09:14 AM

I now see what you mean by rule #3.
I was trying to use the keyboard for the symbol "#".
Anyone reading this, don't make the same mistake, it's the "#" symbol you can click on in the screen where you type you messages/questions.

**shg** · 07-13-2008, 01:39 PM

That function can be simplified and made faster:

Please Login or Register  to view this content.

You can also do this with a formula:

=OR(A1 = {2,3}, AND(MOD(A1, ROW( INDIRECT("2:" & INT( SQRT(A1) ) ) ) ) <> 0) )

**173** · 07-13-2008, 02:34 PM

I've been working on it also, and came up with:

Please Login or Register  to view this content.

This works with the module I've been using, but that module has definately been bogging down my proccessing time. I think I'll try the version you just provided me with. Hopefully I won't have to refresh three time before getting all the numbers to fill in now. Once again Excel forum has done it again. Thanks for all the help.