Test if cell value is in an array

[Rita Brasher]

I want to find out if the contents of a cell are in a predetermined
list. I don't want to have to keep writing an "If" statement for each
entry in the list.

Example:

Array = (Apples, oranges, grapes, bananas}

I want to measure the contents of the cell to determine if they are in
my array. If yes, put a 1, if no put 0. (Will sum 1s later for totals)


I know there's a relatively simple way to do this, but I can't seem to
grasp it right now.

Thanks in advance for any help you can provide!!
Rita

 

[Gary''s Student]

How about:

Function within(r As Range) As Integer
within = 0
s = Array("Apples", "oranges", "grapes", "bananas")
v = r.Value
For i = 0 To 3
If v = s(i) Then
within = 1
End If
Next
End Function