Posted by Brian P. on June 28, 0100 6:06 AM

Thank you Ryan it works fine.

Posted by Brian P. on July 06, 0100 6:59 AM

Thanks mads, but I need the UDF. My UDF myNumber is just an example because my actual UDF is much more complicated and does not have anything to do with determining if is number or the number is >10; but I used myNumber UDF to simply the problem; if I get myNumber to work like ISNUMBER, I will apply necessary code to my complicated UDF.

Posted by Ryan on June 27, 0100 9:42 AM

Hey,

Just one little oversight. You had it looping through all cells, so basically it would just look at the last one and return that value even if it passed through a False cell. You had to have had it exit the function when it finds the false cell. Here is the new code. Hope it helps. Let me know!

Ryan

Function myNUMBER(MYRANGE As Range) As Boolean

Application.Volatile

Dim CELL As Range

For Each CELL In MYRANGE

If CELL.Value > 10 Then

myNUMBER = True

Else

myNUMBER = False

Exit Function

End If

Next

End Function

Posted by Brian P. on July 05, 0100 11:53 AM

Please help because it does not work!!!!!!!!!

The damned thing does not work like ISNUMBER function. Anybody any advice?

Posted by Brian P. on June 28, 0100 12:32 PM

Ryan it worked for the case that I initially presented but now in a new situation does not work. I would like this UDF to work like worksheet ISNUMBER. Say B1=11; B2=22; B3=BLANK; B4=33; B5=44; B6=BLANK.

If I use in B7 myNumber function is FALSE (same if I use ISNUMBER) but if I enter (as arrray or not) in C1=MYNUMBER($B$1:$B$B6), in C2 to C6 the same formula I do not get C1=TRUE;C2=TRUE;C3=FALSE;C4=TRUE;C5=TRUE;C6=FALSE, I get FALSE in all cells. I would like MYNUMBER to work exactly like ISNUMBER. I will appreciate very much any help. Thank you.

Posted by Ryan on June 29, 0100 2:54 PM

I don't know if I can manipulate multiple cells using a UDF, I could do it using a regular Procedure. But I did try out what you are trying to do. Here is what it looks like:

B C

11 =myNumber(B1) => True

22 =myNumber(B2) => True

=myNumber(B3) => False

33 =myNumber(B4) => True

44 =myNumber(B5) => True

=myNumber(B6) => False

This worked fine for me. I just entered in C1 =myNumber(B1), then copied down the column, and copied the formula, it automatically changed the cell reference(B1, B2, etc). Try this. It's not an array but it gets the job done. Let me know.

Ryan

Posted by mads on July 05, 0100 7:25 PM

Re: Please help because it does not work!!!!!!!!!

If you want it to work like ISNUMBER, why not just use ISNUMBER?

mads

Posted by Brian P on June 29, 0100 7:27 PM

No Ryan, it doesnt work how I need. I need to work exactly how Isnumber works.

If I have in B2=2; B3=empty; B4=3; B5=text; B6=3 and build in A2, A3, A4, A5,A6 formula =ISNUMBER($B$2:$B$6) , I get TRUE, FALSE, TRUE, FALSE, TRUE. Similarly if I paste the same formula to the right of the range of reference in C2 to C6 I get the same results. When the formula is build to the left or right of the range, the formula in every cell makes reference to just one cell from the range. If I paste the same formula above, below or corner of the range of reference ($B$2:$B$6), ISNUMBER refers to the whole range and the result is FALSE.

Unfortunately myNumber($B$2:$B$6) when is build on the right or left of the range $B$2:$B$6 ,it refers the whole range instead just every individual cell (B2, B3, B4, B5, B6). For my project I need myNumber to behave exactly like ISNUMBER, I need to use the range not just one cell. Hope it makes sense. Thank you so much.

Posted by mads on July 05, 0100 9:53 PM

I think this works :-

=IF(ISERROR(AND(ISNUMBER($B$2:$B$6),($B$2:$B$6)>10)),"FALSE",AND(ISNUMBER($B$2:$B$6),($B$2:$B$6)>10))

mads