Use

nameShort = Application.WorksheetFunction.VLookup( _
nameLong, Range("RangeAbbreviations"), 2)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)


"Builder" wrote in message
news:%pxVh.8457$vD4.7422@bigfe9...
Why does this VBA code throw Err 1004: failed to get the Vlookup Property
of blah blah

dim nameLong as String, nameShort as String
nameLong = "Here is a long name"
nameShort = Application.WorksheetFunction.VLookup(nameLong,
RangeAbbreviations, 2)

where RangeAbbreviations is a named range in the workbook.


Also, putting the named range in quotes...

nameShort = Application.WorksheetFunction.VLookup(nameLong,
"RangeAbbreviations", 2)

...also fails

Adding a formula to the sheet itself works perfectly.

Any ideas?

Thanks