Can someone help me create a loop to tackle this task? Named Ranges based on cell values using offset

[BuJay]

I have a list of stings as shown below:
Period_Int, Period, Total_NonPort_Units, etc.

I recorded a macro to figure out how to create named ranges for each utilizing offset to make the references dynamic.

However, I have like 50,000 names - I'd like to have a loop that goes through each name below and correctly creates a named range.

The below VBA is what I am doing currently....for every single named range...

Sub Macro1()

ActiveWorkbook.Names.Add Name:="Period_Int", RefersToR1C1:="=OFFSET(results!R1C1,0,5,1,COUNTA(results!R2)-5)"
ActiveWorkbook.Names.Add Name:="Period", RefersToR1C1:="=OFFSET(results!R1C1,1,5,1,COUNTA(results!R2)-5)"
ActiveWorkbook.Names.Add Name:="Total_NonPort_Units", RefersToR1C1:="=OFFSET(results!R1C1,2,5,1,COUNTA(results!R2)-5)"
ActiveWorkbook.Names.Add Name:="Total_NonPort_Units_Pct", RefersToR1C1:="=OFFSET(results!R1C1,3,5,1,COUNTA(results!R2)-5)"
ActiveWorkbook.Names.Add Name:="Total_NonPort_Dollars", RefersToR1C1:="=OFFSET(results!R1C1,4,5,1,COUNTA(results!R2)-5)"
ActiveWorkbook.Names.Add Name:="Total_NonPort_Dollars_Pct", RefersToR1C1:="=OFFSET(results!R1C1,5,5,1,COUNTA(results!R2)-5)"
ActiveWorkbook.Names.Add Name:="Total_NonPort_MMs", RefersToR1C1:="=OFFSET(results!R1C1,6,5,1,COUNTA(results!R2)-5)"
ActiveWorkbook.Names.Add Name:="Total_NonPort_Avg_Dollars", RefersToR1C1:="=OFFSET(results!R1C1,7,5,1,COUNTA(results!R2)-5)"

'etc.....like 50,000 times...

End Sub

 

[davesexcel]

You can loop through a selection, or a specific range

Sub CreateNames()
Dim c As Range
For Each c In Selection  'or a specific range
c.Name = c
Next c
End Sub

I'm not sure what you are naming

 

[BuJay]

You can loop through a selection, or a specific range

Sub CreateNames()
Dim c As Range
For Each c In Selection  'or a specific range
c.Name = c
Next c
End Sub

I'm not sure what you are naming

The first Name is Period_Int and it is defined as =OFFSET(results!R1C1,0,5,1,COUNTA(results!R2)-5)
The second Name is Period and it is defined as =OFFSET(results!R1C1,1,5,1,COUNTA(results!R2)-5)
The third Name is Total_NonPort_Unitsand it is defined as =OFFSET(results!R1C1,2,5,1,COUNTA(results!R2)-5)

You can see that the underlines portion of the formula increases each time and the name is provided in a cell in the sheet.

So, effectively, I am looking for a macro loop that can take something like the below and create named ranges as listed in column A and define them using formulas in column B

 

[BuJay]

Hopefully this clarifies the request

 

[BuJay]

Sub List_To_NamedRange()

    Dim source_worksheet As Worksheet
    Dim i As Long, last_row As Long
   
 Set source_worksheet = ThisWorkbook.Worksheets("Sheet1")
    With source_worksheet
        last_row = .Cells(Rows.Count, "A").End(xlUp).Row
       
        For i = 1 To last_row
            '// add namedrange item
            .Names.Add .Cells(i, "A").Value, "=OFFSET($A$1," & i - 1 & ", 1, 1, COUNTA($1:$1)-1)"
        Next i
   
    End With
   
    Set source_worksheet = Nothing

End Sub