Custom Function
 

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.

Custom Function
 

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.

Custom Function
 

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.

Custom Function
 

You could just use an array formula:

=(SUM((J9=G$10:G$13)*(J9<=H$10:H$13))0)

.....where J9 is the value you are testing and G10:G13 are the lower
table values and H10:H13 are the upper table values.

Martin wrote:
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.

Custom Function
 

I tried your suggestion and it seems to do what I want. I tried to use this
formula as part of an IF statement and get an error. I included the curly
braces around the array formula inside the IF

Can this array formula be used inside an IF?

Thanks.

"Randy Harmelink" wrote:

You could just use an array formula:

=(SUM((J9=G$10:G$13)*(J9<=H$10:H$13))0)

.....where J9 is the value you are testing and G10:G13 are the lower
table values and H10:H13 are the upper table values.

Martin wrote:
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.

Custom Function
 

OK, if you want a function:

Function fIsBetween(ByRef LookUpTable As Range, ByRef LookUpValue As Range)
' example call: =fIsBetween($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
fIsBetween = 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
fIsBetween = 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
fIsBetween = 1 ' set to "found"
Exit Function
End If
Next
End Function

But looks like the Array Entered function would give you a quick answer

Regards

Trevor


"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.

Custom Function
 

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.

Custom Function
 

{=IF((SUM((F1=C$2:C$5)*(F1<=D$2:D$5))0), "found","not found")}

Array Entered again

Note that this is *not* case sensitive, whereas your data appears to be. Is
that important ?

The function I wrote treats "b" and "B" differently ... do you care ?

Regards

Trevor


"Martin" wrote in message
...
I tried your suggestion and it seems to do what I want. I tried to use
this
formula as part of an IF statement and get an error. I included the curly
braces around the array formula inside the IF

Can this array formula be used inside an IF?

Thanks.

"Randy Harmelink" wrote:

You could just use an array formula:

=(SUM((J9=G$10:G$13)*(J9<=H$10:H$13))0)

.....where J9 is the value you are testing and G10:G13 are the lower
table values and H10:H13 are the upper table values.

Martin wrote:
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.

Custom Function
 

Actually, my data is not case sensitive, but I can deal with that.

I have been trying your function and the array formula and have problems
with both.
--When I use your function, it is always returning a 0.
--when I use the array formula, it is working in some instances, but not
others.

This is a rather quick review. I am working with these more. I am thinking
I may have to break my lookup table between numeric and text to solve this.



"Trevor Shuttleworth" wrote:

{=IF((SUM((F1=C$2:C$5)*(F1<=D$2:D$5))0), "found","not found")}

Array Entered again

Note that this is *not* case sensitive, whereas your data appears to be. Is
that important ?

The function I wrote treats "b" and "B" differently ... do you care ?

Regards

Trevor


"Martin" wrote in message
...
I tried your suggestion and it seems to do what I want. I tried to use
this
formula as part of an IF statement and get an error. I included the curly
braces around the array formula inside the IF

Can this array formula be used inside an IF?

Thanks.

"Randy Harmelink" wrote:

You could just use an array formula:

=(SUM((J9=G$10:G$13)*(J9<=H$10:H$13))0)

.....where J9 is the value you are testing and G10:G13 are the lower
table values and H10:H13 are the upper table values.

Martin wrote:
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.

Custom Function
 

In both cases we would need to know what range contains the lookup table and
what cell contains the lookup value. And, ideally the data for the table
and the lookup value. And the actual formula you are using in both cases.

Both methods worked for me but the case sensitivity makes a difference. My
function gives 0 or not found for lower case b but the array function gives
TRUE or found. Your choice.

With regard to the table, that is not a problem for either Randy's array
function or my UDF.

I suspect that you haven't defined the range correctly or perhaps you have
not made the rows absolute ($) ... in fact, if I were a betting man, I'd put
money on it.

Regards


"Martin" wrote in message
...
Actually, my data is not case sensitive, but I can deal with that.

I have been trying your function and the array formula and have problems
with both.
--When I use your function, it is always returning a 0.
--when I use the array formula, it is working in some instances, but not
others.

This is a rather quick review. I am working with these more. I am
thinking
I may have to break my lookup table between numeric and text to solve
this.



"Trevor Shuttleworth" wrote:

{=IF((SUM((F1=C$2:C$5)*(F1<=D$2:D$5))0), "found","not found")}

Array Entered again

Note that this is *not* case sensitive, whereas your data appears to be.
Is
that important ?

The function I wrote treats "b" and "B" differently ... do you care ?

Regards

Trevor


"Martin" wrote in message
...
I tried your suggestion and it seems to do what I want. I tried to use
this
formula as part of an IF statement and get an error. I included the
curly
braces around the array formula inside the IF

Can this array formula be used inside an IF?

Thanks.

"Randy Harmelink" wrote:

You could just use an array formula:

=(SUM((J9=G$10:G$13)*(J9<=H$10:H$13))0)

.....where J9 is the value you are testing and G10:G13 are the lower
table values and H10:H13 are the upper table values.

Martin wrote:
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.

Custom Function
 

In both cases, I am using a Range Name for the lookup table and a single cell
reference for the lookup value.

The formula I am using for the function is: =IsBetween(ARANGE, h10) where
ARANGE is the name for my lookup table.

The array formula is essentially the same as posted earlier except that I am
using a Range Name for the lookup table and using an appropriate cell
reference.

I am at a complete loss. I guess I may end up doing this the hard way.


"Trevor Shuttleworth" wrote:

In both cases we would need to know what range contains the lookup table and
what cell contains the lookup value. And, ideally the data for the table
and the lookup value. And the actual formula you are using in both cases.

Both methods worked for me but the case sensitivity makes a difference. My
function gives 0 or not found for lower case b but the array function gives
TRUE or found. Your choice.

With regard to the table, that is not a problem for either Randy's array
function or my UDF.

I suspect that you haven't defined the range correctly or perhaps you have
not made the rows absolute ($) ... in fact, if I were a betting man, I'd put
money on it.

Regards


"Martin" wrote in message
...
Actually, my data is not case sensitive, but I can deal with that.

I have been trying your function and the array formula and have problems
with both.
--When I use your function, it is always returning a 0.
--when I use the array formula, it is working in some instances, but not
others.

This is a rather quick review. I am working with these more. I am
thinking
I may have to break my lookup table between numeric and text to solve
this.



"Trevor Shuttleworth" wrote:

{=IF((SUM((F1=C$2:C$5)*(F1<=D$2:D$5))0), "found","not found")}

Array Entered again

Note that this is *not* case sensitive, whereas your data appears to be.
Is
that important ?

The function I wrote treats "b" and "B" differently ... do you care ?

Regards

Trevor


"Martin" wrote in message
...
I tried your suggestion and it seems to do what I want. I tried to use
this
formula as part of an IF statement and get an error. I included the
curly
braces around the array formula inside the IF

Can this array formula be used inside an IF?

Thanks.

"Randy Harmelink" wrote:

You could just use an array formula:

=(SUM((J9=G$10:G$13)*(J9<=H$10:H$13))0)

.....where J9 is the value you are testing and G10:G13 are the lower
table values and H10:H13 are the upper table values.

Martin wrote:
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.

Custom Function
 

I think you would need two named ranges for the array function, one for the
low values and another for the high values. A range name seems fine in the
UDF I provided.

{=IF((SUM((H1=LowValues)*(H1<=HighValues))0), "found","not found")}

(array entered)

Incidentally, {=(SUM((H1=LowValues)*(H1<=HighValues)))} gives you 1 or 0
rather than TRUE or FALSE

Can't understand why the UDF is not working for you. If ARange refers only
to the header row, that would result in all the returned values being zero.

For example, ARange refers to =Sheet3!$C$1:$D$1

Regards


"Martin" wrote in message
...
In both cases, I am using a Range Name for the lookup table and a single
cell
reference for the lookup value.

The formula I am using for the function is: =IsBetween(ARANGE, h10) where
ARANGE is the name for my lookup table.

The array formula is essentially the same as posted earlier except that I
am
using a Range Name for the lookup table and using an appropriate cell
reference.

I am at a complete loss. I guess I may end up doing this the hard way.


"Trevor Shuttleworth" wrote:

In both cases we would need to know what range contains the lookup table
and
what cell contains the lookup value. And, ideally the data for the table
and the lookup value. And the actual formula you are using in both
cases.

Both methods worked for me but the case sensitivity makes a difference.
My
function gives 0 or not found for lower case b but the array function
gives
TRUE or found. Your choice.

With regard to the table, that is not a problem for either Randy's array
function or my UDF.

I suspect that you haven't defined the range correctly or perhaps you
have
not made the rows absolute ($) ... in fact, if I were a betting man, I'd
put
money on it.

Regards


"Martin" wrote in message
...
Actually, my data is not case sensitive, but I can deal with that.

I have been trying your function and the array formula and have
problems
with both.
--When I use your function, it is always returning a 0.
--when I use the array formula, it is working in some instances, but
not
others.

This is a rather quick review. I am working with these more. I am
thinking
I may have to break my lookup table between numeric and text to solve
this.



"Trevor Shuttleworth" wrote:

{=IF((SUM((F1=C$2:C$5)*(F1<=D$2:D$5))0), "found","not found")}

Array Entered again

Note that this is *not* case sensitive, whereas your data appears to
be.
Is
that important ?

The function I wrote treats "b" and "B" differently ... do you care ?

Regards

Trevor


"Martin" wrote in message
...
I tried your suggestion and it seems to do what I want. I tried to
use
this
formula as part of an IF statement and get an error. I included the
curly
braces around the array formula inside the IF

Can this array formula be used inside an IF?

Thanks.

"Randy Harmelink" wrote:

You could just use an array formula:

=(SUM((J9=G$10:G$13)*(J9<=H$10:H$13))0)

.....where J9 is the value you are testing and G10:G13 are the
lower
table values and H10:H13 are the upper table values.

Martin wrote:
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.