How can I use a Cell Value as a Named Range in VBA?

Question

So my sample document has two worksheets. In Sheet2, column A is filled with hyperlinks to named cells that are in Sheet1. For reference, I need to copy the name of the named range that each link is referencing into another sheet where I will also record other items from cells whose locations are relative to the location of the named cell. The problem seems to be when I try to set the range from a cell value that has the name of a named cell inside it.

Example

Sheet2 (Named "Document map")

 |A                                             |B|
1|Link (address is #_123 which is a named cell) | |
2|Link 2 (address is #_89 which is a named cell)| |
3|Normal text cell, link location not recorded  | |

Sheet1

 |A                       |B                      |C|
1|(named _123)            |relevant info from _123| |
2|relevant info from _123 |                       | |
3|                        |relevant info from _123| |
4|relevant info from _123 |                       | |
5|(named _89)             |                       | |
6|                        |relevant info from _89 | |
7|relevant info from _89  |                       | |

My VBA

Option Explicit
Public Sub getOrderHeaderLocations()
    Dim rng As range
    Dim c As range
    Dim a As Hyperlink
    Dim r As Integer
    Dim tr As range
    Dim map As Worksheet
    Dim transList As Worksheet
    Dim table As Worksheet
    Set map = Sheets("Document map")
    Set transList = Sheets("Sheet1")
    Set table = Sheets.Add
    table.range("A1").Value = "Named Cell"
    table.range("B1").Value = "Info 1"
    table.range("C1").Value = "Info 2"
    table.range("D1").Value = "Info 3"
    map.Activate
    Set rng = range(map.range("A1"), "A" & map.range("A1").CurrentRegion.Rows.Count)
    'use a counter because not all lines have links and I don't want empty rows
    r = 2 'start in the second row of the table sheet (after headers)
    For Each c In rng.Cells
        If c.Hyperlinks.Count > 0 Then
            Set a = c.Hyperlinks.Item(1)
            table.range("A" & r).Value = a.SubAddress
            r = r + 1
        End If
    Next
    ' Glorious, I have a list of the cell names

    table.Activate
    ' set range to the list of names that we made
    Set rng = range(table.range("A2"), "A" & table.range("A2").CurrentRegion.Rows.Count)
    For Each c In rng.Cells
        r = c.Row
        ' set a range from the value of c which has the reference name of a cell)
        tr = range(c.Value) ' Error 91: Object variable or With block variable not set
        table.range("B" & r).Value = transList.range("B" & tr.Row).Value
    Next
End Sub

Where I'm getting error 91, I'm trying to set a range from the value of a cell. I know that the cell has a name that does exist, and I can verify that by typing in the value (_123) in the name box in excel. I'm using Excel 2010.

When setting a Range you need to use Set... Set tr = Range(c.Value) — Chrismas007
Wow, it's always the little things! I'm not sure how I missed that, thank you! — shamsup

Chrismas007 Chrismas007 · Accepted Answer · 2016-02-29T18:03:00

When setting a Range you need to use Set

Set tr = Range(c.Value)

How can I use a Cell Value as a Named Range in VBA?

1 Answers