Need help to correct this error of my VBA function

[nphamdn]

Hi guys,

I have a function like this

Function newor(num, num1, num2, num3)
Application.Volatile
num1 = Range("Sheet1!B1").Value
If (num = num1 Or num = num2 Or num = num3) Then
newor = True
Else
newor = False
End If
End Function

This function works wells when i type =newor(1,2,5,6). It will compare value of B1 cell to 2,5,6. If one of these number match B1 value, newor = true. But when i type =newor(1,2,5) the result is #VALUE!. So how to make it run with changeable number of input variable.
Ex:
=newor(1,2,4) Compare B1 cell with 2 and 4. If there is one match --->True
=newor(1,2,4,6,9) Compare B1 cell with 2,4,6,9. If there is one match --->True
=newor(1,2,4,7,10,11) Compare B1 cell with 2,4,7,10,11 . If there is one match --->True

Thank you very much.

 

[BQardi]

Use this UDF:

Function newor(CompareValue As Double, ParamArray num() As Variant) As Boolean
Application.Volatile
Dim i As Long
newor = False
For i = LBound(num) To UBound(num)
    If CompareValue = num(i) Then newor = True
Next i
End Function

How to use:
In any cell input your formula:
This will compare the value in B1 with the numbers entered:

=newor(B1,1,3,4,7,12)

the numbers can be cell references:

=newor(B1,B2,B3,B4)

Also this is not restricted to only check B1 you can use any cell you want, even in another sheet:

=newor('Sheet2'!A6,F6,G6,H6,I6,3,6,9,3*2,B6/B7)

 

[nphamdn]

Cant say how much my appreciate is. Thank you very much. You help me a lot. Have a nice day, BQardi. You are awesome.

Nguyen,

 

[BQardi]

You are most welcome...

 

[nphamdn]

Dear BQardi,

Could you help me this last question. I want to build UDF like this
newand(x1,x2;y1,y2;z1,z2........i1,i2) with x1, y1,z1, i1 is the value at Cell x1,y1,z1 and x2,y2,z2, is the compare value. If value at cell x1=x2, and cell y1=y2 and cell z1=z2 and cell i1=i2 ---> TRUE

Thank you very much.
Nguyen

 

[BQardi]

Something like this:

Function newand(ParamArray num() As Variant) As Boolean
Application.Volatile
Dim i As Long
newand = False
For i = LBound(num) To UBound(num) Step 2
    If i + 1 = UBound(num) Or i + 2 = UBound(num) Then Exit For
    If num(i) = num(i + 1) Then newand = True
Next i
End Function

This will compare the values in pairs. So first compare if the two first values are equal to each other, then the next two values, then the next two... and so on...
So:
=newand(1,1,2,3) would equate to TRUE
(1=1 TRUE, 2=3 FALSE) at least one pair is TRUE so the whole function returns TRUE
but:
=newand(1,2,2,3) would equate to FALSE
(1=2 FALSE, 2=3 FALSE) not a single pair is TRUE so the whole function returns FALSE

 

[BQardi]

I suddenly had a doubt. Do you want it so all pairs should be equal with each other before the function should return TRUE?
Then a small change is needed:

Function newand(ParamArray num() As Variant) As Boolean
Application.Volatile
Dim i As Long
newand = [COLOR=#ff0000]True[/COLOR]
For i = LBound(num) To UBound(num) Step 2
    If i + 1 = UBound(num) Or i + 2 = UBound(num) Then Exit For
    If [COLOR=#ff0000]Not [/COLOR]num(i) = num(i + 1) Then newand = [COLOR=#ff0000]False[/COLOR]
Next i
End Function

 

[nphamdn]

Thanks BQardi. That 's exactly what i need. Thank you very much. I like the way you respond my request. Some other forums, when i posted my question, they asked me "why do you need this function? what is your purpose? we think your purposes do not make sense, blabla....." You do not care what is my purpose for this UDF, (though it may be little crazy), you solve it directly and quickly. Such a nice supporter! Thanks again.

Nguyen

 

[BQardi]

Haha, I also don't like it when people ask that. Who cares what the functions are used for.
I have come to the conclusion that people who need to question a question in that way, just don't have the imagination to see what it is used for or don't understand that some people are in a "learning fase" and maybe should get their own experience in regards to whether something is usefull or not...