VBA: Set Range Name with String variable

Question

I'm having an issue which appears to be simple, but I can't figure out what's the problem with my code.

Dim x As Long, y As Long, z As Long
Dim ContractName As String
Dim RangeToName As Range

y = Cells(1, 1).End(xlToRight).Column

    For x = 1 To y
        ContractName = Cells(1, x)
        z = Cells(Rows.count, x).End(xlUp).Row
        Set RangeToName = Range(Cells(2, x), Cells(z, x))
        RangeToName.Name = ContractName
    Next x
End Sub

I'm trying to name ranges based on the text in the "ContractName" cell which will change for each x.

But the line "RangeToName.Name = ContractName" gives me a run-time 450 error.

However if I add any other text in that line for example "RangeToName.Name = "test" & ContractName" everything works and my ranges are named with the full string of text including the text in the "ContractName" cell...

What am I missing?

Cheers

Have you tried ContractName = Cells(1,x).Value or ContractName = Cells(1,x).Text ? — RubberDuck
– RubberDuck, Commented Mar 17, 2015 at 11:53
@pnuts Hi, I'm trying to name each successive range (RangeToName) with the specific text present in the first cell above each column (ContractName). — vlad88667
– vlad88667, Commented Mar 17, 2015 at 12:00
A name must begin with a letter or underscore, does not contain spaces and must not be the same as a reserved keyword in Excel. In other words: A name can't begin with a digit. — Axel Richter
– Axel Richter, Commented Mar 17, 2015 at 12:00

Sophie Altair · Accepted Answer · 2018-02-07 16:35:20Z

3

A name must begin with a letter or underscore, may not contain spaces, and must not be the same as a reserved keyword in Excel.

For example:

A name cannot begin with a digit.

A name cannot be "This is a test"

A name cannot be "A1"

"KL2000" is not usable as a name because would be in conflict with the cell reference KL2000, but "_KL2000" would be usable.

You should always attempt what you're trying to do with VBA directly in the Excel Application. If you had done this with a name like "KL2000", you would have gotten a meaningful error message.

edited Feb 7, 2018 at 16:35

Sophie Altair

2631 gold badge4 silver badges13 bronze badges

answered Mar 17, 2015 at 12:16

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

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

1 Comment

user4039065 Over a year ago

Good information on this subject at Excel Names -- Named Ranges.

Collectives™ on Stack Overflow

VBA: Set Range Name with String variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related