VBA Code to Name Range

[OilEconomist]

Hello and thanks in advance for your help and I will provide feedback.

Cells A4 through A28 have the name I would like to use as NameRanges for cells in Cells C4 through C28.

"I am getting a run-time error '1004': The name that you entered is not valid." on the following line:

Worksheets("1").Names.Add Name:=RangeNameString1, RefersTo:=NameRange1

Entire Code:

Sub RangeNameOneCell()


    Dim i As Integer
    Dim RangeNameString1 As String
    Dim NameRange1 As Range


    
    Worksheets("1").Activate


    For i = 4 To 28


            If ActiveSheet.Cells(i, 1).Value <> "" Then
    
            RangeNameString1 = ActiveSheet.Cells(i, 1).Value
            
            Set NameRange1 = ActiveSheet.Range("C" & i)
                
            Worksheets("1").Names.Add Name:=RangeNameString1, RefersTo:=NameRange1
                
         End If
         
         Next i




End Sub

Also same error on the following code:

Sub RangeName2()
    Dim i As Integer
     
    Dim RangeNameString1 As String
    Dim RangeNameString2 As String


    Dim NameRange1 As Range
    
    Worksheets("1").Activate


    For i = 35 To 41


            If ActiveSheet.Cells(i, 4).Value <> "" Then
    
            RangeNameString1 = ActiveSheet.Cells(i, 4).Value


            Set NameRange1 = ActiveSheet.Range(Cells(i, 7), Cells(i, 1207))
                
            Worksheets("1").Names.Add Name:=RangeNameString1, RefersTo:=NameRange1
            
                
         End If
         
         Next i


End Sub

 

[OilEconomist]

All, I just started typing them in manually and copied the cells contents and noticed that I was using something like "T01" versus "T.01" as the Range Name. T01 is not allowed. I apologize, but cannot figure out the error in the second code.

 

[Fluff]

What error message do you get?

 

[OilEconomist]

What's up Fluff! Thanks for your response, and apologies for the late response, but I've been working 12+ hour days.

I just tested it again on a blank workbook with those respective cells and I get the same error message on both of them. The first one worked out after I changed my the actual Range Names from "T1", "T2", etc..... to "T1", "T2", etc.

"Run-time error '1004': The name that you entered is not valid." on the following line:

Worksheets("1").Names.Add Name:=RangeNameString1, RefersTo:=NameRange1

For the following code:

[COLOR=#333333]
[/COLOR]Sub RangeName2()
    Dim i As Integer
     
    Dim RangeNameString1 As String
    Dim RangeNameString2 As String


    Dim NameRange1 As Range
    
    Worksheets("1").Activate


    For i = 35 To 41


            If ActiveSheet.Cells(i, 4).Value <> "" Then
    
            RangeNameString1 = ActiveSheet.Cells(i, 4).Value


            Set NameRange1 = ActiveSheet.Range(Cells(i, 7), Cells(i, 1207))
                
            Worksheets("1").Names.Add Name:=RangeNameString1, RefersTo:=NameRange1
            
                
         End If
         
         Next i


End Sub

 

[DanteAmor]

"T1" is not a valid name, because there is a cell with that address. You can put "T_1"
Also check that there are no spaces in the cell data, for example "T _ 1 "

 

[OilEconomist]

I apologize. I forgot to hit the "."

I realized that mistake and fixed that with T.1, T.2, etc.

 

[OilEconomist]

I just wanted to clarify this so there was no confusion. This issue has not been fixed.

I just tested it again on a blank workbook with those respective cells and I get the same error message.


"Run-time error '1004': The name that you entered is not valid." on the following line:

Worksheets("1").Names.Add Name:=RangeNameString1, RefersTo:=NameRange1

For the following code:

[COLOR=#333333]
[/COLOR]Sub RangeName2()
    Dim i As Integer
     
    Dim RangeNameString1 As String
    Dim RangeNameString2 As String


    Dim NameRange1 As Range
    
    Worksheets("1").Activate


    For i = 35 To 41


            If ActiveSheet.Cells(i, 4).Value <> "" Then
    
            RangeNameString1 = ActiveSheet.Cells(i, 4).Value


            Set NameRange1 = ActiveSheet.Range(Cells(i, 7), Cells(i, 1207))
                
            Worksheets("1").Names.Add Name:=RangeNameString1, RefersTo:=NameRange1
            
                
         End If
         
         Next i

 

[MARK858]

So what is the value of RangeNameString1 when the error occurs? of course if it is literally a blank workbook you would get the error as you can't have range name that is blank/nothing.

 

[OilEconomist]

It was not completely blank. Apologies for the misunderstanding. I started with a blank workbook and then filled appropriate cells in to test it. I wanted to make sure it wasn't because of something else in the workbook I was using.

 

[MARK858]

Still the same question then...

So what is the value of RangeNameString1 when the error occurs?