CODEWITHSUNDEEP
vbaexcel
vlad88667
vlad88667

Reputation: 59

VBA: Set Range Name with String variable

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

Upvotes: 2

Views: 3405

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

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.

Upvotes: 3

Related Questions