retreiving names cell values in VB

[dcanham]

I'm having trouble retreiving the value from a named cell on another sheet in the same workbook in VB.

ex:
Workbook name = "Test"
Sheet1 has cell named "INV"
Sheet2 has a macro that needs to retreive the value from the named cell in Sheet1.

** here is the line of code
n_Amount = Workbooks("Test").Worksheets("Sheet1").Cells(Range("[Test]Sheet1!INV").Row, Range("[Test]Sheet1!INV").Column)

** here is the error message I'm getting
run time error '1004' method 'range of object _worksheet' failed

I'm thinking this means it can't find the thing I'm indicating? I don't know why? Similar code works for other value calls?

My question is twofold
1) Why would this line be throwing this error?
2) Is there a better way in VB to retreive a value from a named cell on another sheet??

Thanks in advance for any help.

 

[dcanham]

sorry, didn't mean to start such contraversy!

 

[erik.van.geit]

sorry, didn't mean to start such contraversy!

this is good to let our skills and knowledge grow
it's still a mistery why you can't run the line and also what you wanted to do with it, but I presume you were able to get a solution out of these posts

greetings,
Erik

 

[dcanham]

yes thanks....I used sunnyland's

n_amount=Workbooks("Test").Names("inv").RefersToRange.Value

suggestion, and it works well