Worksheet functions in VBA
 

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

Worksheet functions in VBA
 

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

Worksheet functions in VBA
 

Chip Pearson wrote:
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.









"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