Hi,

I'm trying to use Vlookup in a UDF and would like to used a named range I
already have specified as the lookup range. Excel doesn't like the 'raw'
reference.

e.g.

Function Include(Client As Variant, Task As Variant)

If WorksheetFunction.VLookup(Client, ClientTable, 2, False) = "Yes" Then
Include = "Yes"
..... more to come...

End Function

Any suggestiosn please?

You still need to use the Range object, with the name, like

Range("range_name")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jon C" wrote in message
...
Hi,

I'm trying to use Vlookup in a UDF and would like to used a named range I
already have specified as the lookup range. Excel doesn't like the 'raw'
reference.

e.g.

Function Include(Client As Variant, Task As Variant)

If WorksheetFunction.VLookup(Client, ClientTable, 2, False) = "Yes" Then
Include = "Yes"
.... more to come...

End Function

Any suggestiosn please?

In addition to Bob's suggestion, unless you have a compelling reason
not to, I would strongly urge you to include the lookup table as an
argument to the UDF.

For example, if your table is in columns A:B starting with row 1 (row 1
being a header), define the name
ClientTable =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,2)

Now, use the UDF as =include(D2,ClientTable,1)

The revised UDF looks like:
Option Explicit

Function Include(Client As Variant, ClientTable As Range, _
Task As Variant)
If Application.WorksheetFunction.VLookup( _
Client, ClientTable, 2, False) = "Yes" Then
Include = "Yes"
'.... more to come...
Else
Include = "No"
End If
End Function


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

I'm trying to use Vlookup in a UDF and would like to used a named range I
already have specified as the lookup range. Excel doesn't like the 'raw'
reference.

e.g.

Function Include(Client As Variant, Task As Variant)

If WorksheetFunction.VLookup(Client, ClientTable, 2, False) = "Yes" Then
Include = "Yes"
.... more to come...

End Function

Any suggestiosn please?

Terrific. Thanks Bob and Tushar.

Jon