Convert a string to a Range??

[websmythe]

Hi, I'm working in Excel 2003. Is there a way to convert strings to Ranges on the Excel commandline?

I have a lookup function in an estimate sheet that uses 2 ranges, stored as strings, in another worksheet. They are used to return a price from another workbook. When I use named ranges to access them with the lookup function, it doesn't work. But when I cut and paste the strings into the commandline it works prefectly??

I'm stumped... I've looked everywhere I can think of, could anyone offer some suggestions? Thanx.

$A10 = Part Number to search for
 
MaterialOptions is a named Range 
eg: '[Catalog.xls]PriceList'!$A$13:$A$17
 
PriceOptions is a named Range  
eg: '[Catalog.xls]PriceList'!$Z$13:$Z$17
 
LOOKUP($A10,MaterialOptions,PriceOptions) = #VALUE!
But...
LOOKUP($A10,'[Catalog.xls]PriceList'!$A$13:$A$17,'[Catalog.xls]PriceList'!$Z$13:$Z$17) = Success!

 

[websmythe]

To use thos functions in VBA you have to qualify them with their parent, the WorksheetFunction object. Example:

x = WorksheetFunction.VLookup("Range("A1").Value, Range("B1:C100"), 2, False)

Just wanted to say thanks again for the help. Much apreciated. Using worksheetfunction has really payed off. I ended up stumbling across some info on using INDEX and MATCH together which helped solve the problem.