Problems naming cells using VBA

[SmeeZee]

Try running this code in Excel, you should find the first loop works fine, and the cells are named. The second loop fails, why? I have searched the internet but can't find anything on it.


Sub name_those_cells()

Dim i As Long
Dim objRange As Range

Cells(1, 2).Value = "started"

For i = 1 To 56

Range("a" & i).Select
ActiveWorkbook.Names.Add Name:="a_" & i, RefersToR1C1:="=Sheet1!R" & i & "C1"

Next i


Stop


For i = 65480 To 65536

Range("a" & i).Select
ActiveWorkbook.Names.Add Name:="a_" & i, RefersToR1C1:="=Sheet1!R" & i & "C1"

Next i

Cells(1, 2).Value = "finished"
End Sub

 

[SkipVought]

For i = 1 To 56
  with Activesheet
    ActiveWorkbook.Names.Add _
       Name:="a_" & i, _
       RefersTo:="=" & .Name & "!" & .cells(i,"A").Address
  end with
 
Next i

Skip,

_[red][/red]

 

[SmeeZee]

this is fine, does this work though, as it does not for me:

Sub does_it_work()

For i = 65480 To 65536
With ActiveSheet
ActiveWorkbook.Names.Add _
Name:="a_" & i, _
RefersTo:="=" & .Name & "!" & .Cells(i, "A").Address
End With

Next i

end sub

Basically I need to name every cell in the column.

 

[SkipVought]

Works for me.

But WHY?

[A_65536] is also [A65536]

What have you gained?

Skip,

_[red][/red]

 

[SmeeZee]

What version are you using?

Does it work to the completion of the loop?

The name itself is just a test for another program.

 

[SkipVought]

Excel 2003.

yes, to the completion.

Skip,

_[red][/red]

 

[SkipVought]

It's not a good idea to name EVERY cell in a column. Its overhead that is wasted.

Use dynamic ranges based on actual rows/columns utilized.

Skip,

_[red][/red]

 

[SmeeZee]

Agreed, every cell will have different data that could be reference, hence they need to be named. The order could change hence I can't relay on default cell names, I want the value tied to the cell name.

 

[SkipVought]

CAVEAT:

If your sheet name has spaces, add tics to the sheet name...

Sub does_it_work()

For i = 65480 To 65536
  With ActiveSheet
    ActiveWorkbook.Names.Add _
       Name:="a_" & i, _
       RefersTo:="=[red][b]'[/b][/red]" & .Name & "[red][b]'[/b][/red]!" & .Cells(i, "A").Address
  End With
 
Next i

End Sub

Skip,

_[red][/red]