Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert function - custom function name preceded by module name | Excel Programming | |||
Custom Function: Detecting the cell the function is used in | Excel Programming | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
custom function - with built-in function | Excel Programming | |||
Adding a custom function to the default excel function list | Excel Programming |