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

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

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

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 -

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 -