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.

 

[sunnyland]

Hie, there

Try this, :


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

 

[dcanham]

That did the trick sunnyland!!! Thanks for the excellant response. Though I'm still not sure why the other way didn't work.

 

[pgc01]

Hi dcanham

Please try:

n_amount=Range("inv")

HTH
PGC

 

[erik.van.geit]

DELETED because wrong information

 

[Norie]

Erik

If you have a workbook called Test and a named range INV on Sheet1 then this syntax is valid.

MsgBox Range("[Test]Sheet1!INV").Value

 

[erik.van.geit]

I just tested again, Norie,
you are right

dcanham, sorry for the wrong information

still wondering what the purpose of this syntax would be ?
by the way: this line WORKS for me

Workbooks("Test").Worksheets("Sheet1").Cells(Range("[Test]Sheet1!INV").Row, Range("[Test]Sheet1!INV").Column)

REMARK
Cells(Range("[Test]Sheet1!INV").Row, Range("[Test]Sheet1!INV").Column)
is the same as
Range("[Test]Sheet1!INV")
as long as we are on the sheet where the named range belongs to

 

[Norie]

Erik

I don't think you really gave the wrong info.

At first I was skeptical about that syntax too, it's not something I would use/recommend but it does seem to work.

By the way I'm also curious about the OP's syntax too.

 

[sunnyland]

Hello to you all,

I will stick with dcanham as the syntax doesn't work with me either. I am using Office 2000 Excel Version 9.0.2720.

My apologies with the answer I provided before: I forgot the extension, meaning the code will have raise the error suscript out of range
First of all the syntax:

MsgBox Workbooks("Test").Worksheets("Sheet1").Cells.Count

****Return an error suscript out of range
modified as this[including the.xls ] it works:

MsgBox Workbooks("Test.xls").Worksheets("Sheet1").Cells.Count

This:

MsgBox Workbooks("Test.xls").Worksheets("Sheet1").Range("[Test]Sheet1!INV")

Return a runtime error

That works:

MsgBox Workbooks("Test.xls").Worksheets("Sheet1").Range("INV")

same as

Workbooks("Test.xls").Names("inv").RefersToRange.Value

May be it depends on the version of Excel

 

[erik.van.geit]

It works for me also on my "old" PC with Office 2000 version 9.0.