MrExcel Publishing
Your One Stop for Excel Tips & Solutions

True/False macro


Posted by Brian P. on June 27, 2000 8:10 AM

I'm tring to build a UDF that will return True/False if the cells in range meet the condition, but it does not work for the range, it works only for individual cells. Any help greatly appreciated.
My case: B1=33; B2=4; B3=22; B4=333. In B8 I have the UD function: =myNumber(B1:B4) and the result is True instead of False because not all numbers in B1:B4>10.

This is the code:

Function myNUMBER(MYRANGE As Range) As Boolean
Application.Volatile
Dim CELL
For Each CELL In MYRANGE.Cells
If CELL.Value > 10 Then
myNUMBER = True
Else: myNUMBER = False
End If
Next
End Function


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

Thank you Ryan it works fine.

It works fine thanks.

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

Re: Try this formula

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 doesnÂ’t 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

Try this formula


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