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