Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming multiple ranges
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming multiple ranges
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 -- Jim wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming multiple ranges
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 " wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming multiple ranges
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming multiple ranges
nevermind, i figured it out, thanks guys
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming ranges | Setting up and Configuration of Excel | |||
Naming ranges on multiple worksheets | New Users to Excel | |||
Naming Ranges | Excel Discussion (Misc queries) | |||
Naming Ranges | Excel Programming | |||
Naming ranges? | Excel Discussion (Misc queries) |