Structured Reference in UDF

[nodemix2]

Hi,

I am trying to create a UDF for to shorten my formula used in my workbook.
The function does not work and returns a #Value error.
I am new to structured reference, but my research tells me that I can pass a formula as a string to Evaluate in VBA.
Please tell me what is going wrong?

Function HoriVLUp(headerName As String) As Variant

Dim str As String
str = "VLOOKUP([@[Hori.]],HorizontalProfile[#All],MATCH(HorizontalProfile[[#Headers],[" & _
         headerName & _
        "]],HorizontalProfile[#Headers],0),FALSE)"
HoriVLUp = Application.Evaluate(str)

End Function

 

[Fluff]

Hi & welcome to MrExcel.

AFAIK you cannot use the structured reference for the lookup value, try it like

Function HoriVLUp(headerName As String, Cl As Range) As Variant

Dim str As String
str = "VLOOKUP(" & Cl.Address & ",HorizontalProfile[#All],MATCH(HorizontalProfile[[#Headers],[" & _
         headerName & _
        "]],HorizontalProfile[#Headers],0),FALSE)"
HoriVLUp = Application.Evaluate(str)

End Function

and for the formula use =HoriVLUp("something",[@[Hori.]])

 

[nodemix2]

Hi & welcome to MrExcel.

AFAIK you cannot use the structured reference for the lookup value, try it like

Function HoriVLUp(headerName As String, Cl As Range) As Variant

Dim str As String
str = "VLOOKUP(" & Cl.Address & ",HorizontalProfile[#All],MATCH(HorizontalProfile[[#Headers],[" & _
         headerName & _
        "]],HorizontalProfile[#Headers],0),FALSE)"
HoriVLUp = Application.Evaluate(str)

End Function

and for the formula use =HoriVLUp("something",[@[Hori.]])

Works like a charm! Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.