| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why does the following custom function always return a "value" err
I tried to make a function in VBA, which I can use in the worksheet, to
search for the actual root of an item. The context is as follows: - a table containing items on column A and their roots on column F. - the roots are items themselves and most of them have other roots -some roots are references and instead of another item as root, they have the text "-- Unique --" written instead of an item as root. - each item can be traced by multiple iteration to a reference -roots are written somewhat differently than items by adding a set of characters to the item's name, column B contains the root-like name for each item, using a simple =[item cell]&" "&"_Root" formula. - all cells in the table are formatted as "General" I made the following function which should take a reference to a cell as variable and return the end root but instead it returns a value error and I don't know how to correct it. Function rootREF(x As Range) As String Dim device As Range Dim root As String Dim prevroot As String Dim endroot As String Dim table As Range Dim smalltable As Range device = x table = Worksheets("Status").Range(Worksheets("Status").Ra nge("B2"), Worksheets("Status").Range("F500")) smalltable = Worksheets("Status").Range(Worksheets("Status").Ra nge("A2"), Worksheets("Status").Range("F500")) root = CStr(WorksheetFunction.VLookup(device, smalltable, 6, False)) ' the input range is an item located on column A, first root is initialized with the value of the first root for an item If root Like "-- Unique --" Then endroot = "REF" ' if the root of the item in column F is "unique" then it means that the item is a reference Else While Not root Like "-- Unique --" prevroot = root root = CStr(WorksheetFunction.VLookup(prevroot, table, 5, False)) Wend ' if the item is not a reference, search up the tree, using ref-like names this time, until a reference is found endroot = prevroot End If rootREF = endroot ' return "REF" if the item is a reference, return the name of the root reference if the item is not a reference End Function The result should look like the table I'm emulating bellow: Item Ref-like name Root end root(function's result) X1 X1_Root -- Unique-- REF X2 X2_Root X1_Root X1_Root X3 X3_Root X2_Root X1_Root X4 X4_Root X3_Root X1_Root I know this request is quite specific and will not probably be found helpful by other users as well but, if you find the time and find the problem interesting to solve, please give any hints you can. Thank you, Valentin |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why does the following custom function always return a "value"err
Hi
Ranges are objects and must use the Set statement to store as variables e.g. Set device = x Put this in for all your Range objects and see how far that gets you regards Paul On Nov 20, 1:42*pm, VDU wrote: I tried to make a function in VBA, which I can use in the worksheet, to search for the actual root of an item. The context is as follows: - a table containing items on column A and their roots on column F. - the roots are items themselves and most of them have other roots -some roots are references and instead of another item as root, they have the text "-- Unique --" written instead of an item as root. - each item can be traced by multiple iteration to a reference -roots are written somewhat differently than items by adding a set of characters to the item's name, column B contains the root-like name for each item, using a simple =[item cell]&" "&"_Root" formula. - all cells in the table are formatted as "General" I made the following function which should take a reference to a cell as variable and return the end root but instead it returns a value error and I don't know how to correct it. Function rootREF(x As Range) As String Dim device As Range Dim root As String Dim prevroot As String Dim endroot As String Dim table As Range Dim smalltable As Range device = x table = Worksheets("Status").Range(Worksheets("Status").Ra nge("B2"), Worksheets("Status").Range("F500")) smalltable = Worksheets("Status").Range(Worksheets("Status").Ra nge("A2"), Worksheets("Status").Range("F500")) root = CStr(WorksheetFunction.VLookup(device, smalltable, 6, False)) ' the input range *is an item located on column A, first root is initialized with the value of the first root for an item If root Like "-- Unique --" Then * * endroot = "REF" ' if the root of the item in column F is "unique" then it means that the item is a reference Else * * While Not root Like "-- Unique --" * * * * prevroot = root * * * * root = CStr(WorksheetFunction.VLookup(prevroot, table, 5, False)) * * Wend ' if the item is not a reference, search up the tree, using ref-like names this time, until a reference is found endroot = prevroot End If rootREF = endroot ' return "REF" if the item is a reference, return the name of the root * reference if the item is not a reference End Function The result should look like the table I'm emulating bellow: Item * Ref-like name * Root * * * * * * * * * *end root(function's result) X1 * * * X1_Root * * * * * * * -- Unique-- * * * *REF X2 * * * X2_Root * * * * * * * X1_Root * * * * * * *X1_Root X3 * * * X3_Root * * * * * * * X2_Root * * * * * * *X1_Root X4 * * * X4_Root * * * * * * * X3_Root * * * * * * *X1_Root I know this request is quite specific and will not probably be found helpful by other users as well but, if you find the time and find the problem interesting to solve, please give any hints you can. Thank you, Valentin |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why does the following custom function always return a "value"
Hello Paul,
Thank you for your reply, I added Set for the first 3 lines, those are the only places where ranges appear, the others are strings, now I get a #NAME? error. I also shortened the expressions that define table and smalltable to table = Worksheets("Status").Range("B2:F500") smalltable = Worksheets("Status").Range("A2:F500") Thank you, Valentin " wrote: Hi Ranges are objects and must use the Set statement to store as variables e.g. Set device = x Put this in for all your Range objects and see how far that gets you regards Paul On Nov 20, 1:42 pm, VDU wrote: I tried to make a function in VBA, which I can use in the worksheet, to search for the actual root of an item. The context is as follows: - a table containing items on column A and their roots on column F. - the roots are items themselves and most of them have other roots -some roots are references and instead of another item as root, they have the text "-- Unique --" written instead of an item as root. - each item can be traced by multiple iteration to a reference -roots are written somewhat differently than items by adding a set of characters to the item's name, column B contains the root-like name for each item, using a simple =[item cell]&" "&"_Root" formula. - all cells in the table are formatted as "General" I made the following function which should take a reference to a cell as variable and return the end root but instead it returns a value error and I don't know how to correct it. Function rootREF(x As Range) As String Dim device As Range Dim root As String Dim prevroot As String Dim endroot As String Dim table As Range Dim smalltable As Range device = x table = Worksheets("Status").Range(Worksheets("Status").Ra nge("B2"), Worksheets("Status").Range("F500")) smalltable = Worksheets("Status").Range(Worksheets("Status").Ra nge("A2"), Worksheets("Status").Range("F500")) root = CStr(WorksheetFunction.VLookup(device, smalltable, 6, False)) ' the input range is an item located on column A, first root is initialized with the value of the first root for an item If root Like "-- Unique --" Then endroot = "REF" ' if the root of the item in column F is "unique" then it means that the item is a reference Else While Not root Like "-- Unique --" prevroot = root root = CStr(WorksheetFunction.VLookup(prevroot, table, 5, False)) Wend ' if the item is not a reference, search up the tree, using ref-like names this time, until a reference is found endroot = prevroot End If rootREF = endroot ' return "REF" if the item is a reference, return the name of the root reference if the item is not a reference End Function The result should look like the table I'm emulating bellow: Item Ref-like name Root end root(function's result) X1 X1_Root -- Unique-- REF X2 X2_Root X1_Root X1_Root X3 X3_Root X2_Root X1_Root X4 X4_Root X3_Root X1_Root I know this request is quite specific and will not probably be found helpful by other users as well but, if you find the time and find the problem interesting to solve, please give any hints you can. Thank you, Valentin |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why does the following custom function always return a "value"
Hi
In your Vlookup try device.value or Cstr(device.value). You also need to be sure that your device is in the lookup table. regards Paul On Nov 20, 3:20*pm, VDU wrote: Hello Paul, Thank you for your reply, I added Set for the first 3 lines, those are the only places where ranges appear, the others are strings, now I get a #NAME? error. I also shortened the expressions that define table and smalltable to table = Worksheets("Status").Range("B2:F500") smalltable = Worksheets("Status").Range("A2:F500") Thank you, Valentin " wrote: Hi Ranges are objects and must use the Set statement to store as variables e.g. Set device = x Put this in for all your Range objects and see how far that gets you regards Paul On Nov 20, 1:42 pm, VDU wrote: I tried to make a function in VBA, which I can use in the worksheet, to search for the actual root of an item. The context is as follows: - a table containing items on column A and their roots on column F. - the roots are items themselves and most of them have other roots -some roots are references and instead of another item as root, they have the text "-- Unique --" written instead of an item as root. - each item can be traced by multiple iteration to a reference -roots are written somewhat differently than items by adding a set of characters to the item's name, column B contains the root-like name for each item, using a simple =[item cell]&" "&"_Root" formula. - all cells in the table are formatted as "General" I made the following function which should take a reference to a cell as variable and return the end root but instead it returns a value error and I don't know how to correct it. Function rootREF(x As Range) As String Dim device As Range Dim root As String Dim prevroot As String Dim endroot As String Dim table As Range Dim smalltable As Range device = x table = Worksheets("Status").Range(Worksheets("Status").Ra nge("B2"), Worksheets("Status").Range("F500")) smalltable = Worksheets("Status").Range(Worksheets("Status").Ra nge("A2"), Worksheets("Status").Range("F500")) root = CStr(WorksheetFunction.VLookup(device, smalltable, 6, False)) ' the input range *is an item located on column A, first root is initialized with the value of the first root for an item If root Like "-- Unique --" Then * * endroot = "REF" ' if the root of the item in column F is "unique" then it means that the item is a reference Else * * While Not root Like "-- Unique --" * * * * prevroot = root * * * * root = CStr(WorksheetFunction.VLookup(prevroot, table, 5, False)) * * Wend ' if the item is not a reference, search up the tree, using ref-like names this time, until a reference is found endroot = prevroot End If rootREF = endroot ' return "REF" if the item is a reference, return the name of the root * reference if the item is not a reference End Function The result should look like the table I'm emulating bellow: Item * Ref-like name * Root * * * * * * * * * *end root(function's result) X1 * * * X1_Root * * * * * * * -- Unique-- * * * *REF X2 * * * X2_Root * * * * * * * X1_Root * * * * * * *X1_Root X3 * * * X3_Root * * * * * * * X2_Root * * * * * * *X1_Root X4 * * * X4_Root * * * * * * * X3_Root * * * * * * *X1_Root I know this request is quite specific and will not probably be found helpful by other users as well but, if you find the time and find the problem interesting to solve, please give any hints you can. Thank you, Valentin- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why does the following custom function always return a "value"
Hello,
Of all the mistakes I could have made :). Thank you Paul for your much appreciated help. Best Regards, Valentin " wrote: Hi In your Vlookup try device.value or Cstr(device.value). You also need to be sure that your device is in the lookup table. regards Paul On Nov 20, 3:20 pm, VDU wrote: Hello Paul, Thank you for your reply, I added Set for the first 3 lines, those are the only places where ranges appear, the others are strings, now I get a #NAME? error. I also shortened the expressions that define table and smalltable to table = Worksheets("Status").Range("B2:F500") smalltable = Worksheets("Status").Range("A2:F500") Thank you, Valentin " wrote: Hi Ranges are objects and must use the Set statement to store as variables e.g. Set device = x Put this in for all your Range objects and see how far that gets you regards Paul On Nov 20, 1:42 pm, VDU wrote: I tried to make a function in VBA, which I can use in the worksheet, to search for the actual root of an item. The context is as follows: - a table containing items on column A and their roots on column F. - the roots are items themselves and most of them have other roots -some roots are references and instead of another item as root, they have the text "-- Unique --" written instead of an item as root. - each item can be traced by multiple iteration to a reference -roots are written somewhat differently than items by adding a set of characters to the item's name, column B contains the root-like name for each item, using a simple =[item cell]&" "&"_Root" formula. - all cells in the table are formatted as "General" I made the following function which should take a reference to a cell as variable and return the end root but instead it returns a value error and I don't know how to correct it. Function rootREF(x As Range) As String Dim device As Range Dim root As String Dim prevroot As String Dim endroot As String Dim table As Range Dim smalltable As Range device = x table = Worksheets("Status").Range(Worksheets("Status").Ra nge("B2"), Worksheets("Status").Range("F500")) smalltable = Worksheets("Status").Range(Worksheets("Status").Ra nge("A2"), Worksheets("Status").Range("F500")) root = CStr(WorksheetFunction.VLookup(device, smalltable, 6, False)) ' the input range is an item located on column A, first root is initialized with the value of the first root for an item If root Like "-- Unique --" Then endroot = "REF" ' if the root of the item in column F is "unique" then it means that the item is a reference Else While Not root Like "-- Unique --" prevroot = root root = CStr(WorksheetFunction.VLookup(prevroot, table, 5, False)) Wend ' if the item is not a reference, search up the tree, using ref-like names this time, until a reference is found endroot = prevroot End If rootREF = endroot ' return "REF" if the item is a reference, return the name of the root reference if the item is not a reference End Function The result should look like the table I'm emulating bellow: Item Ref-like name Root end root(function's result) X1 X1_Root -- Unique-- REF X2 X2_Root X1_Root X1_Root X3 X3_Root X2_Root X1_Root X4 X4_Root X3_Root X1_Root I know this request is quite specific and will not probably be found helpful by other users as well but, if you find the time and find the problem interesting to solve, please give any hints you can. Thank you, Valentin- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error when running Solver ("No return or halt function found...") | Excel Programming | |||
| function to return day in the form "Monday", "Tuesday" etc given . | Excel Worksheet Functions | |||
| Check if cells contain the word "Thailand", return "TRUE" | Excel Worksheet Functions | |||
| "No RETURN() or HALT() function found on macro sheet." | Excel Worksheet Functions | |||
| Trying to create a custom "clear worksheet" function (in a VBA module) | Excel Programming | |||
Hybrid Mode
