Worksheet functions in VBA

[Chip Pearson]

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]

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

 

[Builder]

Use

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

Thanks Chip,

That threw another error "Method Range of the worksheet class .. failed"

But I figured it out: nameShort =
Application.WorksheetFunction.VLookup(nameLong,
Worksheets("shortNames").Range("A1:B100"), 2)


Thanks for the prompt reply.