Looping Formula to define Names

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
Having trouble getting a simple script to work. I want to define the contents of columns to the table header name as a defined name.

so that means that if in column A I have a range of data A2:A10 I would want that defined as a name. The name would = A1.

I know the rows may vary with different data sets loaded. I want it to loop from A1 to CA1.

Unsure how to put the Namedef into the loop.
Code:
Public Sub NameDef()

Dim NameDef As String
Dim LastRow

LastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
For Each NameDef In Worksheets("Sheet1").Range(Cells(1, 1), Cells(LastRow, 74)).Cells
  If NameDef IsText = True Then
    NameDef.Name = "NameDef"
  End If
Next NameDef
End Sub
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
try:
Code:
Public Sub NameDef()
Dim LastRow
With Sheets("Sheet1")
  LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
  For Each cll In .Range("A1:CA1").Cells
    If Application.IsText(cll.Value) Then
      Range(cll.Offset(1), .Cells(LastRow, cll.Column)).Name = cll.Value
    End If
  Next cll
End With
End Sub
 
Upvote 0
Just trying this function. Code breaks at this line. What could cause that error.

Code:
Range(cll.Offset(1), .Cells(LastRow, cll.Column)).Name = cll.Value
 
Upvote 0
Just trying this function. Code breaks at this line. What could cause that error.

Code:
Range(cll.Offset(1), .Cells(LastRow, cll.Column)).Name = cll.Value
Chiefly, an empty cell in the top row, or a cell containing illegal characters for a name - a space say..

Choose debug when it error out and int he immediate pane (Ctrl+g if it's not visible) type
?"xxx" & cll.value & "xxx"
and press enter. It should give the contents of the cell being processed. What does it give?
 
Last edited:
Upvote 0
Code:
?"xxx" & cll.value & "xxx"
xxxid5xxx
Seems to mean column E doesn't it? Could be wrong.

column E is a group identifier by number so some may belong to class 1 or class 2 etc. The column only contains numbers though so if you are in class 1 column E contains 1 next to the name.
Code:
  <table style="border-collapse: collapse; width: 62pt;" border="0" cellpadding="0" cellspacing="0" width="82"><col style="width: 62pt;" width="82">  <tbody><tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; width: 62pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border: 0.5pt solid white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23" width="82">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">1</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">2</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td class="xl63" style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">2</td>  </tr> </tbody></table>
 
Last edited:
Upvote 0
Having trouble getting a simple script to work. I want to define the contents of columns to the table header name as a defined name.
so that means that if in column A I have a range of data A2:A10 I would want that defined as a name. The name would = A1.
I know the rows may vary with different data sets loaded. I want it to loop from A1 to CA1.
If I understand correctly, here's something you might try.
Code:
Public Sub NameDef()
Dim NameDef As Range
Dim LastRow As Long
With Worksheets("Sheet1")
  For Each NameDef In .Range(.Cells(1, 1), .Cells(1, 79))
    If WorksheetFunction.IsText(NameDef) Then
      LastRow = .Cells(Rows.Count, NameDef.Column).End(xlUp).Row
        ActiveWorkbook.Names.Add Name:=NameDef.Value, RefersToR1C1:= _
        "=Sheet1!R2C" & NameDef.Column & ":R" & LastRow & "C" & NameDef.Column
    End If
  Next NameDef
End With
End Sub

Hope it helps.
 
Upvote 0
Code:
?"xxx" & cll.value & "xxx"
xxxid5xxx
Seems to mean column E doesn't it? Could be wrong.

column E is a group identifier by number so some may belong to class 1 or class 2 etc. The column only contains numbers though so if you are in class 1 column E contains 1 next to the name.
At first sight I don't see why this should fail (I did test my code). 2 approaches:
1. What is the error message?
2. Private Mail me at this site (MrExcel) and I'll send you an email address to send me (a version of) the file.

In the meantime, do any Names at all get defined?
 
Upvote 0
Yes there are 15 columns that get defined correctly. With the previous question
Code:
?"xxx" & cll.value & "xxx"
xxxid5xxx</pre>

I only just realised "id5" is the name of a column. It as the name suggests is an id feild containing a set of numbers unique as an id.

Code:
  <table style="border-collapse: collapse; width: 41pt;" border="0" cellpadding="0" cellspacing="0" width="55"><col style="width: 41pt;" width="55">  <tbody><tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; width: 41pt; font-size: 11pt; color: white; font-weight: 700; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 1.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(114, 124, 163);" height="23" width="55">id5</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">114287</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">116887</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">117654</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">113187</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">119136</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(226, 228, 236);" align="right" height="23">131760</td>  </tr>  <tr style="height: 17.25pt;" height="23">   <td style="height: 17.25pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: "Gill Sans MT"; border-width: medium 0.5pt 0.5pt; border-style: none solid solid; border-color: -moz-use-text-color white white; background: none repeat scroll 0% 0% rgb(198, 202, 218);" align="right" height="23">109153</td>  </tr> </tbody></table>
 
Upvote 0
HalfAce tested your code and it has the same error. think I understand it though. The is an illegal character or space that it can't convert or use as a name, I think.

4862707537

http://www.flickr.com/photos/46087571@N04/4862707537/
 
Upvote 0
A simpler option would be to use this

Code:
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.CreateNames Top:=True
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top