Should have taken the "f" of my version of the function ;-)

Function IsBetween(ByRef LookUpTable As Range, ByRef LookUpValue As Range)
' example call: =IsBetween($C$2:$D$5,F1)
Application.Volatile
Dim LUT_Rows As Long
Dim LUT_Columns As Integer
Dim LUT_FirstRow As Integer
Dim LUT_FirstColumn As Integer
Dim i As Long
IsBetween = 0 ' set to default "not found"
LUT_Rows = LookUpTable.Rows.Count
LUT_Columns = LookUpTable.Columns.Count
LUT_FirstRow = LookUpTable.Row
LUT_FirstColumn = LookUpTable.Column
If LUT_Columns < 2 Then
IsBetween = 3 ' set to "error"
Exit Function
End If
For i = LUT_FirstRow To LUT_FirstRow + LUT_Rows - 1
If LookUpValue.Value = Cells(i, LUT_FirstColumn).Value _
And LookUpValue.Value <= Cells(i, LUT_FirstColumn + 1).Value Then
IsBetween = 1 ' set to "found"
Exit Function
End If
Next
End Function


"Martin" wrote in message
...
Here is my code. It isn't pretty, but I want to get it working.

Thanks.

Function IsBetween(LookTable, CheckValue) As Integer
Dim LookArray()
' LookTable = "ARANGE"
' CheckValue = 11000
CurrWkst = ActiveSheet.Name
LookWkst = Range(LookTable).Worksheet.Name

Worksheets(LookWkst).Select
Range(LookTable).Select
'Find last row
Selection.End(xlDown).Select
Last_Row = ActiveCell.Row
'Move back to first row
Selection.End(xlUp).Select
First_Row = ActiveCell.Row
NumOfRows = Last_Row - First_Row
'Resize array and load array with lookup table
ReDim LookArray(1 To NumOfRows, 1 To 2)
For X = 1 To NumOfRows
ActiveCell.Offset(1, 0).Select
LookArray(X, 1) = ActiveCell.Value
LookArray(X, 2) = ActiveCell.Offset(0, 1).Value
Next X
Worksheets(CurrWkst).Select
'Search lookup array for value
For Y = 1 To NumOfRows
If Y = 1 And CheckValue < LookArray(Y, 1) Then
IsBetween = 0
Exit For
End If
If CheckValue = LookArray(Y, 1) And CheckValue <= LookArray(Y, 2)
Then
IsBetween = 1
Exit For
End If
Next Y
End Function



"Trevor Shuttleworth" wrote:

post your code please


"Martin" wrote in message
...
I am in the process of trying to create a Function that will take a
value
and
determine if it appears in a table of ranges. For example, my table of
ranges may be:
Lower Upper
10000 15000
30000 35000
A C
g i

If I use a value of 11000, my function would return a 1 since it is in
one
of the ranges. A value of d would return a 0.

This all works fine as long as I do not use this function as a formula
in
a
cell. When I do use it in a cell, the #VALUE results, regardless of
whether
the value is in a range or not.

I guess I have two questions:
1) Has someone done something like this and have any suggestions?
2) Can anyone give me an idea of why I am getting the #VALUE in the
cell?

Thanks.