BuJay
Board Regular
- Joined
- Jun 24, 2020
- Messages
- 73
- Office Version
- 365
- 2019
- 2016
- 2013
- Platform
- Windows
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
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