Why does the following custom function always return a "value" err

V

VDU

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").Range("B2"),
Worksheets("Status").Range("F500"))
smalltable = Worksheets("Status").Range(Worksheets("Status").Range("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
 
P

paul.robinson

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

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 andI
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").Range("B2"),
Worksheets("Status").Range("F500"))
smalltable = Worksheets("Status").Range(Worksheets("Status").Range("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
Click to expand...
 
V

VDU

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

[email protected] said:
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

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").Range("B2"),
Worksheets("Status").Range("F500"))
smalltable = Worksheets("Status").Range(Worksheets("Status").Range("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
Click to expand...
Click to expand...
 
P

paul.robinson

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

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



[email protected] said:
Hi
Ranges are objects and must use the Set statement to store as
variables
Click to expand...
e.g. Set device = x
Click to expand...
Put this in for all your Range objects and see how far that gets you
regards
Paul
Click to expand...
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 cellas
variable and return the end root but instead it returns a value errorand 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").Range("B2"),
Worksheets("Status").Range("F500"))
smalltable = Worksheets("Status").Range(Worksheets("Status").Range("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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
V

VDU

Hello,

Of all the mistakes I could have made :). Thank you Paul for your much
appreciated help.

Best Regards,
Valentin

[email protected] said:
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

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



[email protected] said:
Hi
Ranges are objects and must use the Set statement to store as
variables
Click to expand...
e.g. Set device = x
Click to expand...
Put this in for all your Range objects and see how far that gets you
regards
Paul
Click to expand...
I tried to make a function in VBA, which I can use in the worksheet, to
search for the actual root of an item.
Click to expand...
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"
Click to expand...
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.
Click to expand...
Function rootREF(x As Range) As String
Click to expand...
Dim device As Range
Dim root As String
Dim prevroot As String
Dim endroot As String
Dim table As Range
Dim smalltable As Range
Click to expand...
device = x
table = Worksheets("Status").Range(Worksheets("Status").Range("B2"),
Worksheets("Status").Range("F500"))
smalltable = Worksheets("Status").Range(Worksheets("Status").Range("A2"),
Worksheets("Status").Range("F500"))
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
endroot = prevroot
End If
Click to expand...
rootREF = endroot
' return "REF" if the item is a reference, return the name of the root
reference if the item is not a reference
Click to expand...
End Function
Click to expand...
The result should look like the table I'm emulating bellow:
Click to expand...
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
Click to expand...
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.
Click to expand...
Thank you,
Valentin- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to Mimic an Array Function in VBA 1
Find the correct sheet then find a value on that sheet 8
Use a VBA function to return a factor 2
function to return a "sub-range" 6
Function to find last value in a column 10
Why does this not work 7
Modify Function to Return Value 2
Unique values in a Collection 4

Top