Getting a value from a named range in VBA

Question

I want to retrieve a value from named range. Imagine a named range that has X columns and Y rows. I want to return a value, e.g., from column 2, row 3. The issue I experience is that if I write the code and run it, Excel throws an error. If I write the code into the watch window, it returns fine. See below

...
Dim NamedRange As Variant: NamedRange = Range(NamedRangeName)
...
Dim ReturnValue As Object
Set ReturnValue = NamedRange(RowIndex, ColumnToRetrieveIndex) 'Throws Run-time error 424. Object required

If I write NamedRange(RowIndex, ColumnToRetrieveIndex) into the watch window, I can see the correct value of the cell.

I don't know VB much so I guess it's just some kind of syntax error how I want to pass it into the ReturnValue but I just can't figure it out.

Barry · Accepted Answer · 2014-10-07 18:59:05Z

31

Use this

ThisWorkbook.Names("myNamedRange").RefersToRange(1,1)

To get the value from the first cell in the named range "myNamedRange"

With ThisWorkbook.Names you can access all named ranges of all the sheets within the current workbook. With RefersToRange you get a reference to the actual range.

edited Oct 7, 2014 at 18:59

answered Oct 7, 2014 at 18:54

Barry

3,7331 gold badge20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Unoembre Over a year ago

This was useful but if you have scoped you name to a particular worksheet in Names Manager you may need to consider something like ActiveSheet.Names.

AjV Jsy · Accepted Answer · 2019-10-29 11:10:21Z

5

This looks like a good place to put a handy note (as it's a top search result):
If you name a cell "TopLeft", then Sheets(1).Range("TopLeft").Value gets the contents, and Sheets(1).Range("TopLeft").Offset(2,3).Value gets the value from 2 down, 3 across from there.

answered Oct 29, 2019 at 11:10

AjV Jsy

6,1054 gold badges39 silver badges30 bronze badges

Collectives™ on Stack Overflow

Getting a value from a named range in VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related