Referencing Named Ranges in Range Function (Excel - VBA)

0
votes

I'm trying to write a macro that takes a string variable as an input, with the string variable referencing a named range.

Currently what I have is:

Sub SubItems()

Dim M As String
    M = "=R[-1]C"
    'where M refers to row above, currently it is Manufacturers

Dim g As Range

Set g = Range(" & M & ")

ActiveCell.Value = g(2)
'For Example

End Sub

The problem is with the Set g = Range(" & M & ") syntax

I want the input argument for the Range function to be what M is, and not the literal letter M. Similar to how in C you would do printf('%s', M) for example.

Edit:

Currently how I have the excel sheet setup, is that you select a main item from a drop down menu. Then I want to select the cell below the main item and automatically fill in the rows with sub items. The sub items are stored in a named range that is named after the main item.

Hence I want my macro to automatically read the row above it (Main Item) hence why I have M = "=R[-1]C". Then I want to input that into the range function and that's the problem I'm currently facing.

I hope this clarifies my problem more clearly.

3
vbaexcelsyntaxnamed-ranges
Do you just mean Set g = Range(M)? Or Set g = Range("""" & M & """")? - BruceWayne
@ SherifR Your explanation is not helping, what do you want to do? You have 2 strings, one is the name of the named range and one is the value of that? - Ibo
What are you actually trying to do here? Because at a first glance, it looks like you're trying to make a simple problem harder than it needs to be. Take a look at this: meta.stackexchange.com/questions/66377/what-is-the-xy-problem - SandPiper
IF your name Range is part of a table use Range(Table 1[Name]). I believe this will work for any name ranged but I have only used it for tables. Also your missing the closing and opening parentheses on Set g I think your looking for Set g = Range("Stuff here" & M & "Stuff here") - Quint

3 Answers

0
votes

The problem is that your variable M describes a relative reference, and I do not believe you can apply a relative reference to a Range object in the manner you describe. Perhaps you want to attack your problem from a different angle? For instance:

You could use your method but rather set an absolute reference, e.g.

M = "A5"
Debug.Print Range(M).Value

Or alternatively you could specify a relative reference using code such as:

debug.print activecell.Offset(-1,0).Value
0
votes

String literals are encased in double quotes, so you're essentially referencing a named Range called & M & .

If you're using a string variable, remember that its value is surrounded by quotes as well. So it should just be Range(M), or Range("=R[-1]C"), the two are the same.

Note: =R[-1]C is a very strange name, are you sure you meant this? Make sure you understand the difference between a named range and what's in the range (it looks like a formula)! Perhaps some description on what =R[-1]C is and I can help you more?

0
votes

What I have understood so far - you have a named range, called M and you want to assign it to a VBA range. If this is the case, this is the code to achieve it:

Sub TestMe()

    Dim g As Range
    Set g = ActiveSheet.[M]
    Debug.Print g.Address

End Sub