VBA Code Run Time Error on Double Loop To Name Range

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
172
Thanks in advance for any suggestions for which I will provide feedback.

I am trying to name range cells which start on row 6 and end on 51 between columns D (column 4) and Z (column 26).

The names are a combination of what's in row 6 through 51 in column A and columns D through X in row 1.

Why am I getting "Run-time error '1004' Application-defined or object-defined error" on the following line of code:
Code:
  Set NameRange1 = ActiveSheet.Range(i, j)

Entire Code:
Code:
Sub RangeNameOneCell()


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


    
    Worksheets("1").Activate


    For i = 6 To 51
    
        For j = 4 To 26


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




End Sub
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
172
Apologies. I just figured it out after hours of messing with it. I cannot use range in that manner. When I change to Cells, it works.

Basically change

Code:
Set NameRange1 = ActiveSheet.Range(i, j)
to
Code:
Set NameRange1 = ActiveSheet.Cells(i, j)
 

Forum statistics

Threads
1,085,495
Messages
5,383,997
Members
401,871
Latest member
allemandi

Some videos you may like

This Week's Hot Topics

Top