VBA Dynamic name Range

Question

I have a small/complex problem, but I cannot understand why I'm obtaining this error.

In a vba code I have this:

ws = Sheets("Calendar") c = "Fixed" first = ce.Address end = ce2.Address wb.Names.Add Name:=c, RefersTo:=ws.Name & "!" & first & ":" & end

The formula create a named range like this one:

="Calendar!$M$2:$M$4"

This named range doesn't show any values, but if I change manually the named range in this:

=Calendar!$M$2:$M$4

It will work.

Where is the issue? Why the formula doesn't work? Which parameter is wrong?

Thanks

Multiple issues with your code. ws = Sheets("Calendar") should be Set ws = Sheets("Calendar") and end cannot be a variable name. But the main issue is that the string for RefersTo:= must begin with an equal sign. See msdn.microsoft.com/en-us/library/office/ff834743.aspx. — Axel Richter
– Axel Richter, Commented Mar 28, 2016 at 12:22

MikeC · Accepted Answer · 2016-03-28 12:36:02Z

end is not a good variable name
ws is an object, so Set should be used to assign it
The formula needs a "=" in front of it

Set ws = Sheets("Calendar") c = "Fixed" first = ce.Address end1 = ce2.Address wb.Names.Add Name:=c, RefersTo:="=" & ws.Name & "!" & first & ":" & end1

I have translated "first" and "end" without thinking about it. In the original code the name was different. The missing "=" was the problem. Thank you!

Collectives™ on Stack Overflow

VBA Dynamic name Range

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related