naming multiple ranges

[Spencer.Sadkin]

I have tried many different codes and nothing will work. I have a 50 x
10 matrix of names and i want to name each cell in the matrix with the
text that is in the cell.

i have tried the following but get erros:
Sub Macro()

Dim x As Range
For Each x In Selection.Cells
x.Name = x.Text

Next

End Sub

sub macro()

For i=2 to 9
For j=3 to 52
cel=cells(j,i).text
ThisWorkbook.Names.Add Name:=cel _
RefersTo:=cells(j,i)
Next j
Next i

End sub

Any ideas?

 

[Jim Rech]

I think your code is fine but some of your names may be illegal. For
instance "c" is not valid.

Run this modification to your first sub code instead and fix the bad names
that it points out after it runs.

Sub Macro()
Dim x As Range
On Error GoTo BadName
For Each x In Selection.Cells
x.Name = x.Text
NextCell:
Next
Exit Sub
BadName:
MsgBox "Cell " & x.Address & " has a bad name"
Resume NextCell
End Sub

 

[Tom Hutchins]

Try this...

Sub Macro1()
Dim x As Range
On Error Resume Next
For Each x In Selection.Cells
ActiveWorkbook.Names.Add _
Name:=x.Text, _
RefersToR1C1:="=" & ActiveSheet.Name & _
"!" & x.Address(ReferenceStyle:=xlR1C1)
Next
End Sub

If the cell contents would create an invalid range name, no name is created
for that cell. When I tested this (Excel 2003), the macro allowed me to
create range names that I could not create manually. For example, I created a
range name B10 in cell D24. If I use F5 to Goto B10, the cell B10 is
selected, not the named range.

Hope this helps,

Hutch

 

[Spencer.Sadkin]

Thanks tom, when i look in insert-define the name range is there but
the quick box in the top left does not show it. also if i reference
the range in another cell on the same page with =[rangename] i get a
popup box for update values, any ideas?

 

[Spencer.Sadkin]

nevermind, i figured it out, thanks guys