VBA Code to Loop through and NameRange Cells

[OilEconomist]

Hello and thanks in advance and I will provide feedback to any assistance on solutions.

I'm trying to name range a large amount of cells and would like to use a VBA loop to do it. I am naming a column of cells from (20, 3) to (20, 69). That's 50 cells in column C starting on the 20th row and ending on the 69th row. Please note, these range names are only specific to "Sheet2" (a single worksheet) and not the workbook.

I'm getting errors such as "Error" "Object Required" on the line "If i < 10 Then Set RangeName = "T.0" & i" and "&" gets highlighted/

Here is my code thus far:

Sub NameRange1()


    Worksheets("Sheet2").Activate
        
        Dim i As Integer
        
        Dim LastRow As Long
            
    
        For i = 1 To 2
    
            Range(19 + i, 3).Select


            If i < 10 Then Set RangeName = "T.0" & i
            Else: Set RangeName = "T." & i
    
    
        Next i
    
    


End Sub

 

[Fluff]

Is this what you want?

Sub OilEconomist()
   Dim i As Integer
    
   With Worksheets("Sheet2")
      For i = 1 To 20
          .Cells(19 + i, 3).Name = Format(i, "T_00")
      Next i
   End With
End Sub

 

[OilEconomist]

Thank you for your response.

Although I haven’t tried it yet. It seems the code you’ve given is formatting the cells not “Naming” them with Range Name. If I was to manually do I select “Alt A” “M” “M” (Formulas and NameRange a Cell”

Any other solutions?

 

[MARK858]

Might be better to test the code posted before asking for other solutions

 

[OilEconomist]

Thanks Mark858. Apologies and will do!

Fluff thanks so much!

That's close, but what I'm trying to do is name the cells T.01, T.02 ..... T.10, T.11..... T.20

With what you've given it's naming them T_01, T_02, etc. So I changed the "_" to a period and it's giving me T1.00, T2.00, .....T20.00

Also, it's making it refer to the entire workbook. How do you make it just for that specific sheet?

 

[Fluff]

How about

Sub OilEconomist()
   Dim i As Integer
    
   With Worksheets("Sheet2")
      For i = 1 To 20
          .Names.Add "T." & Format(i, "00"), "=Sheet2!R" & i & "C3"
      Next i
   End With
End Sub

 

[OilEconomist]

Thanks Fluff! That worked.

The only thing I did differently was to add 19 as such since I was starting on Row 20

Sub RangeName()
 
    Worksheets("Sheet2").Activate
       
        Dim i As Integer
        Dim ii
       
        With Worksheets("Sheet2")
       
        For i = 1 To 50
            
            
            .Names.Add "T." & Format(i, "00"), "=Sheet2!R" & (i + 19) & "C3"
        
            .Names.Add "F." & Format(i, "00"), "=Sheet2!R" & (i + 19) & "C4"
       
        Next i
  
    End With
   
 
End Sub

 

[Fluff]

Glad you got it sorted & thanks for the feedback