Trying to Create a bunch of named ranges using a simple loop

[bisel]

Hello,

I'm trying to create a bunch of named ranges and rather than manually selecting each cell, I want to use VBA to create the named ranges. Ultimately, I want to create named ranges "photo_item1" to "photo_item500" starting with cell W25

Can anyone tell me what is wrong with this macro's VBA ?

Sub addnamefields()
Dim fieldname As String
Dim fieldnameroot As String

fieldnameroot = "photo_item" 

Range("W25").Select  'This is the initial (starting) cell

For i = 1 To 5
    fieldname = fieldnameroot & i
    ActiveWorkbook.Names.Add fieldname
    ActiveCell.Offset(1, 0).Select  ' Move to next cell, one row down in the same column
Next i

End Sub

Thanks,

Steve

 

[Peter_SSs]

Try like this

Sub addnamefields_v2()
  Dim fieldname As String
  Dim fieldnameroot As String
  Dim i As Long
  Dim c As Range
  
  Const Howmany As Long = 500
  
  fieldnameroot = "photo_item"
  
  For Each c In Range("W25").Resize(Howmany)
    i = i + 1
    fieldname = fieldnameroot & i
    ActiveWorkbook.Names.Add Name:=fieldname, RefersTo:=c
  Next c
End Sub